Use numerical 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().

This topic describes how to use numerical functions in queries and provides examples.

Examples

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

1. Create the toys_order table.

   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 to 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