Use numerical functions in queries

This topic provides specific examples on how to use numeric functions in queries.

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

Calculate the total sales amount of each type of toy and the average sales amount per order.

1. Create a table named toys_order.

   obclient>  CREATE TABLE toys_order (
              order_id     NUMBER,
              toy_id       NUMBER,
              toy_dealer   VARCHAR2(20),
              toy_price    NUMBER(10, 2),
              toy_amount   NUMBER
               );
   

2. Insert data into the toys_order table.

   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
   

3. 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 |
   |      2 |        490 |   163.3333 |
   |      3 |        680 |        340 |
   +--------+------------+------------+
   3 rows in set