Use aggregate functions in queries

This topic provides examples of how to use aggregate functions in queries.

Aggregate functions

An aggregate function scans a set of records and returns a single row. This set of records can be a table, a view, or the result of a subquery.

In grouped queries, aggregate functions are typically used with the GROUP BY clause to group records by one or more column values and return a single row per group.

Examples

Create the sample table toys_order and insert order data.

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

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 queries

Use count(), sum(), avg(), round(), min(), and max() to calculate the sales of toy orders.

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 seekdb, when aggregate functions are used with the GROUP BY clause, the columns in the select_list do not need to match those in the GROUP BY clause. If you require the columns in the select_list to match those in the GROUP BY clause, set the sql_mode in the MySQL command line to ONLY_FULL_GROUP_BY. Example:

SHOW VARIABLES LIKE '%sql_mode%';
+---------------+-------------------------------------------------------+
| Variable_name | Value                                                 |
+---------------+-------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,STRICT_TRANS_TABLES,STRICT_ALL_TABLES |
+---------------+-------------------------------------------------------+
1 row in set 

SET SESSION sql_mode='STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY';                                                                                                                  Query OK, 0 rows affected

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 the current version of seekdb, see the SQL Reference section.