Use numerical functions in queries
This topic provides specific examples on how to use numeric functions in queries.
Numerical functions
General numerical functions include the sum function SUM(), average function AVG(), round-up function CEIL(), round-down function FLOOR(), and rounding function ROUND().
Examples
Create a table named toys_order and insert order data.
obclient> CREATE TABLE toys_order (
order_id INTEGER,
toy_id INTEGER,
toy_dealer VARCHAR(20),
toy_price DECIMAL(10, 2),
toy_amount INTEGER
);
Query OK, 0 rows affected
obclient> INSERT INTO toys_order VALUES ( 1001,1, 'A',53.12,100 ),( 1002,2, 'A',24.8,190 ),( 1003,3, 'A',19.9,330)
,( 1004,1, 'B',53.12,50 ),( 1005,2, 'B',24.8,200 ),( 1006,3, 'B',19.9,350 ),( 1007,2, 'A',24.8,100 );
7 rows in set
Use numerical functions in a query
Use the SUM(), ROUND(), and AVG() functions to query the total sales amount of each type of toy and the average sales amount per order.
obclient> SELECT toy_id
, SUM(toy_amount) sum_amount
, ROUND(avg(toy_amount),4) avg_amount
FROM toys_order GROUP BY toy_id ORDER BY toy_id;
+--------+------------+------------+
| toy_id | sum_amount | avg_amount |
+--------+------------+------------+
| 1 | 150 | 75.000 |
| 2 | 490 | 163.333 |
| 3 | 680 | 340.000 |
+--------+------------+------------+
3 rows in set