Common arithmetic operators used in queries include addition (+), subtraction (-), multiplication (×), division (/), inversion (-), and modulus (MOD). These operators can be used on numeric columns.
This topic describes how to use arithmetic operators in queries and provides examples.
Example: Query the number and unit price of goods purchased by customers who meet conditions
The product of the number of the goods and the unit price is the total payment of the goods. You can add the t2.ol_quantity * t3.i_price column to the select_list table and rename the table item_sum_price.
/*Create a table and insert proper 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');
/*Query the number and unit price of goods purchased by customers who meet conditions.*/
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