This topic describes how to use numeric functions in queries through specific examples.
Numeric functions
Common numeric functions include the sum function SUM(), the average function AVG(), the ceiling function CEIL(), the floor function FLOOR(), and the rounding function ROUND().
Examples
Create a sample 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 numeric functions in queries
Use SUM(), ROUND(), and AVG() to query the total sales amount and average sales amount per order for each toy.
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