Use aggregate functions in queries

An aggregate function scans a set of records and then returns a single row. This set of records can be a table or a view, or results of a subquery.

Aggregate functions are usually used with the GROUP BY clause to group values based on one or more columns, and return one record for each group.

Examples

Gather statistics on the sales amount of toy orders.

1. Create a table named toys_order_ny and enter order data.

   obclient> CREATE TABLE toys_order_ny (
              order_id     NUMBER,
              toy_id       NUMBER,
              toy_dealer   VARCHAR2(20),
              toy_price    NUMBER,
              toy_amount   NUMBER
             );
   Query OK, 0 rows affected
   

2. Insert data into the toys_order_ny table.

   obclient> INSERT INTO toys_order_ny 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. Use an aggregate function to gather statistics on the sales amount of toy orders.

   obclient> SELECT toy_id
             , count(*)                         order_count
             , sum(toy_amount)                  sum_amount
             , round(avg(toy_amount),2)         avg_amount
             , min(toy_amount)                  min_amount
             ,max(toy_amount)                   max_amount
             FROM toys_order_ny GROUP BY toy_id  ORDER BY toy_id;
   +--------+-------------+------------+------------+------------+------------+
   | toy_id | order_count | sum_amount | avg_amount | min_amount | max_amount |
   +--------+-------------+------------+------------+------------+------------+
   |      1 |           2 |        150 |      75.00 |         50 |        100 |
   |      2 |           3 |        490 |     163.33 |        100 |        200 |
   |      3 |           2 |        680 |     340.00 |        330 |        350 |
   +--------+-------------+------------+------------+------------+------------+
   3 rows in set
   

For more information about the aggregate functions supported by OceanBase Cloud of the current version, see SQL syntax (Oracle compatible mode).