This topic describes how to use arithmetic operators in queries through specific examples.
Arithmetic operators
Common arithmetic operators used in queries include + (addition), - (subtraction), * (multiplication), / (division), - (negation), and MOD (modulus). 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');
Examples of using arithmetic operators in queries
Query the quantity and price of the items that meet the conditions. The quantity multiplied by the price gives the total payment for each type of item. Add t2.ol_quantity * t3.i_price to the select_list and rename it item_sum_price.
obclient> 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