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