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 (MySQL 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 a MySQL tenant, if you use an aggregate function together with the GROUP BY clause without specifying any column in the SELECT_LIST clause, it may return a strange result set. If the columns specified in the SELECT_LIST clause must also be in the GROUP BY clause, you need to set the sql_mode to "ONLY_FULL_GROUP_BY" in the MySQL command line. SQL statement:
obclient> 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.00 | 9876.11 |
| 2 | 329 | 1153354.23 | 3505.64 | 0.00 | 9979.34 |
+---------+-------------+------------+------------+------------+------------+
2 rows in set (0.01 sec)
obclient> 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
;
+---------+---------+-------------+------------+------------+------------+------------+
| ol_w_id | ol_d_id | order_count | sum_amount | avg_amount | min_amount | max_amount |
+---------+---------+-------------+------------+------------+------------+------------+
| 1 | 1 | 297 | 917174.33 | 3088.13 | 0.00 | 9876.11 |
| 2 | 1 | 329 | 1153354.23 | 3505.64 | 0.00 | 9979.34 |
+---------+---------+-------------+------------+------------+------------+------------+
2 rows in set (0.00 sec)
obclient> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------+
| sql_mode | PIPES_AS_CONCAT,STRICT_TRANS_TABLES,STRICT_ALL_TABLES |
+---------------+-------------------------------------------------------+
1 row in set (0.00 sec)
obclient> SET SESSION sql_mode='STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec)
obclient> 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 1055 (42000): 'tpccdb.ordl.ol_d_id' is not in GROUP BY
obclient>