This topic describes how to use aggregate functions in queries through specific examples.
Aggregate functions
Aggregate functions scan a set of records and return a single row. This set of records can be a table or view, or the result of a subquery.
In grouped queries, aggregate functions are typically used with the GROUP BY clause. They group the data by one or more columns and return a single row for each group.
Examples
Create the sample table 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
Examples of using aggregate functions in queries
Use count(), sum(), avg(), round(), min(), and max() to calculate 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
When aggregate functions are used with the GROUP BY clause, there are no special requirements for the columns in the select list. If you require the columns in the select list to match those in the GROUP BY clause, set the sql_mode parameter on the MySQL command line to ONLY_FULL_GROUP_BY. 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)
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 details about the aggregate functions supported by the current version of OceanBase AI Database, see the SQL Syntax (User Tenant) chapter.