Use numeric functions in queries

This topic provides an example of how to use numeric functions in queries.

Numeric functions

Common numeric functions include the SUM() function, which calculates the sum of values; the AVG() function, which calculates the average of values; the CEIL() function, which rounds a value up; the FLOOR() function, which rounds a value down; and the ROUND() function, which rounds a value to the nearest integer.

Example

Create the toys_order table and insert order data.

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

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 the SUM(), ROUND(), and AVG() functions to query the total sales and average sales per order for each toy.

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