This topic describes how to use numerical functions in queries and provides examples.
Numerical functions
General numerical functions include the sum function SUM(), average function AVG(), round-up function CEIL(), round-down function FLOOR(), numeric rounding function TRUNC(), and rounding function ROUND().
Examples
Create a table named toys_order and enter 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 queries
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