This topic provides specific examples on how to use aggregate functions in queries.
Aggregate functions
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 a single row for each group.
Examples
Create a table named toys_order and insert 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
Use aggregate functions in a query
Use the count(), sum(), avg(), round(), min(), and max() functions 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 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 compatible mode of OceanBase Cloud, if you use aggregate functions together with the GROUP BY clause, no special requirement is raised for columns in the SELECT list. If you want the columns in the SELECT list to match those in the GROUP BY clause, you must set sql_mode to ONLY_FULL_GROUP_BY in the MySQL CLI tool. Here is an example:
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 Cloud, see SQL syntax (MySQL compatible mode).