Skip to content
elephantoo

Aggregates, GROUP BY & HAVING

Lesson 10 of 31 16 min read

COUNT, SUM, AVG, MIN, MAX, grouping rows, filtering groups and WITH ROLLUP.

So far every query has returned individual rows. Real questions are often about summaries: How much did we sell per month? What's the average order? Which city has the most customers? That's the job of aggregate functions and GROUP BY.

Sample data#

SQL
CREATE TABLE sales (
    id        INT PRIMARY KEY,
    sold_on   DATE NOT NULL,
    region    VARCHAR(10) NOT NULL,
    category  VARCHAR(20) NOT NULL,
    product   VARCHAR(30) NOT NULL,
    qty       INT NOT NULL,
    amount    DECIMAL(8,2) NOT NULL,
    discount  DECIMAL(4,2)
);

INSERT INTO sales VALUES
    (1, '2026-01-05', 'North', 'Electronics', 'Headphones', 2, 300.00, 0.10),
    (2, '2026-01-09', 'South', 'Electronics', 'Charger',    5, 125.00, NULL),
    (3, '2026-01-21', 'North', 'Books',       'SQL Guide',  1,  40.00, NULL),
    (4, '2026-02-02', 'South', 'Books',       'Novel',      3,  45.00, 0.05),
    (5, '2026-02-14', 'North', 'Electronics', 'Speaker',    1, 950.00, 0.15),
    (6, '2026-02-20', 'South', 'Electronics', 'Headphones', 1, 150.00, NULL),
    (7, '2026-03-03', 'North', 'Books',       'SQL Guide',  4, 160.00, 0.10),
    (8, '2026-03-11', 'East',  'Toys',        'Puzzle',     2,  30.00, NULL);

The aggregate functions#

An aggregate takes many rows and returns one value:

SQL
SELECT
    COUNT(*)            AS row_count,
    COUNT(discount)     AS discounted_rows,
    COUNT(DISTINCT product) AS products,
    SUM(amount)         AS revenue,
    AVG(amount)         AS avg_sale,
    MIN(sold_on)        AS first_sale,
    MAX(amount)         AS biggest_sale
FROM sales;
Output
+-----------+-----------------+----------+---------+------------+------------+--------------+
| row_count | discounted_rows | products | revenue | avg_sale   | first_sale | biggest_sale |
+-----------+-----------------+----------+---------+------------+------------+--------------+
|         8 |               4 |        6 | 1800.00 | 225.000000 | 2026-01-05 |       950.00 |
+-----------+-----------------+----------+---------+------------+------------+--------------+
FunctionReturns
COUNT(*)number of rows
COUNT(expr)number of rows where expr is not NULL
COUNT(DISTINCT expr)number of distinct non-NULL values
SUM(expr) / AVG(expr)total / mean of non-NULL values
MIN(expr) / MAX(expr)smallest / largest (works on numbers, text and dates)
GROUP_CONCAT(expr)values joined into one string

Aggregates ignore NULLs (except COUNT(*)). So AVG(discount) averages only the four rows that have a discount. If missing values should count as zero, write AVG(COALESCE(discount, 0)).

GROUP BY: one result row per group#

GROUP BY splits the rows into groups that share a value, then computes the aggregates per group:

SQL
SELECT category, COUNT(*) AS sales, SUM(amount) AS revenue
FROM sales
GROUP BY category
ORDER BY revenue DESC;
Output
+-------------+-------+---------+
| category    | sales | revenue |
+-------------+-------+---------+
| Electronics |     4 | 1525.00 |
| Books       |     3 |  245.00 |
| Toys        |     1 |   30.00 |
+-------------+-------+---------+

Group by several columns to get one row per combination:

SQL
SELECT region, category, SUM(qty) AS units
FROM sales
GROUP BY region, category
ORDER BY region, category;
Output
+--------+-------------+-------+
| region | category    | units |
+--------+-------------+-------+
| East   | Toys        |     2 |
| North  | Books       |     5 |
| North  | Electronics |     3 |
| South  | Books       |     3 |
| South  | Electronics |     6 |
+--------+-------------+-------+

You can group by expressions too. Here is monthly revenue:

SQL
SELECT DATE_FORMAT(sold_on, '%Y-%m') AS month,
       SUM(amount)                   AS revenue,
       ROUND(AVG(amount), 2)         AS avg_sale
FROM sales
GROUP BY month
ORDER BY month;
Output
+---------+---------+----------+
| month   | revenue | avg_sale |
+---------+---------+----------+
| 2026-01 |  465.00 |   155.00 |
| 2026-02 | 1145.00 |   381.67 |
| 2026-03 |  190.00 |    95.00 |
+---------+---------+----------+

The ONLY_FULL_GROUP_BY rule#

Every column in the SELECT list must either be in the GROUP BY or inside an aggregate. Otherwise MySQL 8 refuses to guess:

SQL
SELECT category, product, SUM(amount) FROM sales GROUP BY category;
Output
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.sales.product' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Each category has several products, so which one should be shown? Fix it by grouping by product as well, or by aggregating it:

SQL
SELECT category,
       GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ', ') AS products,
       SUM(amount) AS revenue
FROM sales
GROUP BY category;
Output
+-------------+------------------------------+---------+
| category    | products                     | revenue |
+-------------+------------------------------+---------+
| Books       | Novel, SQL Guide             |  245.00 |
| Electronics | Charger, Headphones, Speaker | 1525.00 |
| Toys        | Puzzle                       |   30.00 |
+-------------+------------------------------+---------+

(ANY_VALUE(product) also silences the error and means "any one product is fine". Use it only when that's really true.) Columns that are functionally dependent on the group, such as other columns of a table whose primary key you grouped by, are allowed.

WHERE vs HAVING#

  • WHERE filters rows before grouping. It can't see aggregates.
  • HAVING filters groups after aggregation. It can use aggregates.
SQL
SELECT product, SUM(qty) AS units, SUM(amount) AS revenue
FROM sales
WHERE sold_on >= '2026-01-01'         -- rows: only 2026
GROUP BY product
HAVING SUM(amount) >= 200             -- groups: only big sellers
ORDER BY revenue DESC;
Output
+------------+-------+---------+
| product    | units | revenue |
+------------+-------+---------+
| Speaker    |     1 |  950.00 |
| Headphones |     3 |  450.00 |
| SQL Guide  |     5 |  200.00 |
+------------+-------+---------+

If a condition doesn't involve an aggregate, put it in WHERE. Filtering rows early is faster than building groups and throwing them away.

Conditional aggregation#

Combine CASE (or IF, or a plain boolean) with aggregates to build pivot-style reports in a single pass:

SQL
SELECT
    region,
    SUM(CASE WHEN category = 'Electronics' THEN amount ELSE 0 END) AS electronics,
    SUM(CASE WHEN category = 'Books'       THEN amount ELSE 0 END) AS books,
    SUM(discount IS NOT NULL)                                       AS discounted_sales,
    SUM(amount)                                                     AS total
FROM sales
GROUP BY region
ORDER BY total DESC;
Output
+--------+-------------+--------+------------------+---------+
| region | electronics | books  | discounted_sales | total   |
+--------+-------------+--------+------------------+---------+
| North  |     1250.00 | 200.00 |                3 | 1450.00 |
| South  |      275.00 |  45.00 |                1 |  320.00 |
| East   |        0.00 |   0.00 |                0 |   30.00 |
+--------+-------------+--------+------------------+---------+

SUM(discount IS NOT NULL) works because a true comparison is 1 and a false one is 0 in MySQL.

Subtotals with WITH ROLLUP#

WITH ROLLUP adds subtotal rows and a grand total. GROUPING() tells you which rows are the extra ones:

SQL
SELECT
    IF(GROUPING(region), 'ALL', region)       AS region,
    IF(GROUPING(category), 'ALL', category)   AS category,
    SUM(amount) AS revenue
FROM sales
GROUP BY region, category WITH ROLLUP;
Output
+--------+-------------+---------+
| region | category    | revenue |
+--------+-------------+---------+
| East   | Toys        |   30.00 |
| East   | ALL         |   30.00 |
| North  | Books       |  200.00 |
| North  | Electronics | 1250.00 |
| North  | ALL         | 1450.00 |
| South  | Books       |   45.00 |
| South  | Electronics |  275.00 |
| South  | ALL         |  320.00 |
| ALL    | ALL         | 1800.00 |
+--------+-------------+---------+

The order of a grouped query#

SQL
SELECT   region, COUNT(*) AS n      -- 5. compute output columns
FROM     sales                      -- 1. take rows
WHERE    amount > 20                -- 2. filter rows
GROUP BY region                     -- 3. form groups
HAVING   COUNT(*) > 1               -- 4. filter groups
ORDER BY n DESC                     -- 6. sort
LIMIT    5;                         -- 7. cut

MySQL lets you use SELECT aliases in GROUP BY, HAVING and ORDER BY, but not in WHERE.

Common mistakes#

  • Putting aggregates in WHERE. WHERE SUM(amount) > 100 is an error ("Invalid use of group function"). Use HAVING.
  • Forgetting NULLs. COUNT(col) and AVG(col) skip NULLs, which may or may not be what you want.
  • Averages of averages. Averaging per-month averages isn't the overall average when months have different numbers of sales. Aggregate from the raw rows.
  • Counting after a join. Joining orders to order items multiplies rows. Use COUNT(DISTINCT o.id) (more in JOINs).
  • GROUP_CONCAT truncation. The result is cut at group_concat_max_len (1,024 bytes by default). Raise it with SET SESSION group_concat_max_len = 100000; if needed.

What's next#

You can query and summarise a single table. Next you'll make tables trustworthy with constraints (primary keys, foreign keys, UNIQUE, CHECK and DEFAULT) before learning to combine tables with JOINs.

Check your understanding

Quick quiz

0/3 answered

  1. 1.What is the difference between COUNT(*) and COUNT(discount)?

  2. 2.You want only categories whose total sales exceed 1000. Where does the condition SUM(amount) > 1000 go?

  3. 3.With MySQL 8's default ONLY_FULL_GROUP_BY mode, why does SELECT category, product, SUM(amount) FROM sales GROUP BY category; fail?

Finished reading?

Mark this lesson complete to track your progress.