An aggregate function scans a set of records and then returns a single row. This set of records can be a table, a view, or the 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 a single row of records for each group.
This topic describes how to use aggregate functions in queries by using examples.
Example: Gather statistics on the sales amount of toy orders
/*Create a table named toys_order table and enter order data.*/
obclient> CREATE TABLE toys_order (
order_id INTEGER,
toy_id INTEGER,
toy_dealer VARCHAR(20),
toy_price DECIMAL(10, 2),
toy_amount INTEGER
);
Query OK, 0 rows affected
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
/*Gather statistics on the sales amount of the 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 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
In the MySQL mode of OceanBase Database, if you use aggregate functions with the GROUP BY clause, no special requirement is raised for the select_list. To keep the columns in the select_list to be consistent with those in the GROUP BY clause, you must set sql_mode to ONLY_FULL_GROUP_BY in the MySQL command-line tool. Sample code:
obclient> SHOW VARIABLES LIKE '%sql_mode%';
+---------------+-------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------+
| sql_mode | PIPES_AS_CONCAT,STRICT_TRANS_TABLES,STRICT_ALL_TABLES |
+---------------+-------------------------------------------------------+
1 row in set
obclient> SET SESSION sql_mode='STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected
obclient> SELECT toy_id,order_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 GROUP BY toy_id ORDER BY toy_id ;
ERROR 1055 (42000): 'test.toys_order.order_id' is not in GROUP BY
For more information about the aggregate functions supported by OceanBase Database, see OceanBase Database SQL Reference (MySQL Mode).