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. For more information about the aggregate functions supported by OceanBase, see OceanBase SQL Reference (Oracle Mode).
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.
Example: Grouping and calculating the sales of each warehouse
In an Oracle tenant, when you use an aggregate function in conjunction with the GROUP BY clause, the SELECT_LIST clause must reference all columns specified in the GROUP BY clause. Otherwise, an error is returned. SQL statement:
SELECT ol_w_id
, count(*) order_count
, sum(ol_amount) sum_amount
, round(avg(ol_amount),2) avg_amount
, min(ol_amount) min_amount
,max(ol_amount) max_amount
FROM ordl
GROUP BY ol_w_id ORDER BY ol_w_id;
+---------+-------------+------------+------------+------------+------------+
| OL_W_ID | ORDER_COUNT | SUM_AMOUNT | AVG_AMOUNT | MIN_AMOUNT | MAX_AMOUNT |
+---------+-------------+------------+------------+------------+------------+
| 1 | 297 | 917174.33 | 3088.13 | 0 | 9876.11 |
| 2 | 329 | 1153354.23 | 3505.64 | 0 | 9979.34 |
+---------+-------------+------------+------------+------------+------------+
2 rows in set (0.01 sec)
SELECT ol_w_id, ol_d_id
, count(*) order_count
, sum(ol_amount) sum_amount
, round(avg(ol_amount),2) avg_amount
, min(ol_amount) min_amount
,max(ol_amount) max_amount
FROM ordl
GROUP BY ol_w_id
ORDER BY ol_w_id;
ERROR-00979: 'TPCC.ORDL.OL_D_ID' not a GROUP BY expression
obclient>