Skip to main content

Use arithmetic operators in queries

This topic provides examples of how to use arithmetic operators in queries.

Arithmetic operators​

Common arithmetic operators in queries include + (addition), - (subtraction), * (multiplication), / (division), - (unary minus), and MOD (modulo). These operators can be applied to columns of numeric data types.

Examples​

Create a sample table and insert appropriate data.

CREATE TABLE cust (
       c_id        INTEGER,
       c_first     VARCHAR(20),
       c_last      VARCHAR(20),
       c_credit    DECIMAL(10, 2)
 );

CREATE TABLE ordr (
       o_id         INTEGER,
       o_ol_cnt     INTEGER,
       o_entry_d    DATE
 );

CREATE TABLE item (
       i_id           INTEGER, 
       i_name       VARCHAR(20),
       i_price       DECIMAL(10, 2)
 );

INSERT INTO cust VALUES(101,'Ann','Smith',16.10);
INSERT INTO cust VALUES(102,'Madeleine','Johnson',23.00);
INSERT INTO cust VALUES(103,'Michael','Brown',9.05);
INSERT INTO ordr VALUES(102,400,'2020-01-23');
INSERT INTO ordr VALUES(103,600,'2020-03-10');
INSERT INTO ordr VALUES(105,300,'2020-09-18');
INSERT INTO ordr VALUES(105,100,'2020-07-01');
INSERT INTO item VALUES(103,'Apple','12.9');
INSERT INTO item VALUES(103,'Banana','10.2');
INSERT INTO item VALUES(105,'Pear','9.8');

Use arithmetic operators in queries​

Query the number and price of items that meet the criteria. The total payment for each category is the product of the quantity and price. You can add a column t2.ol_quantity * t3.i_price to the select_list and rename it to item_sum_price.

SELECT t1.c_first, t1.c_last, t1.c_credit, t2.o_ol_cnt * t3.i_price item_sum_price, t2.o_entry_d, t3.i_name, t3.i_price 
      FROM cust t1
      JOIN ordr t2 ON (t1.c_id=t2.o_id)
      JOIN item t3 ON (t2.o_id=t3.i_id)
      ORDER BY t1.c_id, t2.o_id;
+---------+--------+----------+----------------+------------+--------+---------+
| c_first | c_last | c_credit | item_sum_price | o_entry_d  | i_name | i_price |
+---------+--------+----------+----------------+------------+--------+---------+
| Michael | Brown  |     9.05 |        7740.00 | 2020-03-10 | Apple  |   12.90 |
| Michael | Brown  |     9.05 |        6120.00 | 2020-03-10 | Banana |   10.20 |
+---------+--------+----------+----------------+------------+--------+---------+
2 rows in set

Contents

Edit this page