This topic provides specific examples on how to use arithmetic operators in queries.
Arithmetic operators
Common arithmetic operators used in queries include addition (+), subtraction (-), multiplication (*), division (/), negation (-), and modulus (MOD). These operators can be used on columns of numeric data types.
Examples
Create a table and insert proper data into the table.
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 a query
Query the number and price of commodities that meet the conditions. The product of the number and the price is the total payment of each commodity. To do this, add a column t2.ol_quantity * t3.i_price with the alias item_sum_price to the SELECT list.
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
