SQL GROUP BY and HAVING Tutorial: COUNT, SUM, AVG, ROLLUP and Pivot Reports in MySQL

Totals, averages and counts are the heart of almost every report: sales per branch, students per course, orders per day, the best-selling product this week. In SQL, all of these come from one clause: GROUP BY. Paired with aggregate functions like COUNT, SUM and AVG, and filtered with HAVING, it turns thousands of rows into a short summary you can show on a dashboard.

In this tutorial you will build those reports step by step on a small store database. You will learn the five main aggregate functions, grouping by one or more columns, the difference between WHERE and HAVING, COUNT(DISTINCT), pivot-style reports with SUM(CASE ...), subtotals with WITH ROLLUP, and how to fix the two errors beginners hit most often. Every query was run on MariaDB 11.8 (MySQL-compatible), and the result tables are copied straight from the console.

Step 1: Create the Sample Database

Open phpMyAdmin, MySQL Workbench or the MySQL command line and run this script. It creates a store_reports database with six products and twelve sales from three branches:

Step 2: Aggregate Functions Without GROUP BY

Aggregate functions take many rows and return one value. Without GROUP BY, the whole table is treated as one group:

+--------------+------------+---------+------------+------------+------------+
| transactions | items_sold | revenue | avg_sale   | first_day  | last_day   |
+--------------+------------+---------+------------+------------+------------+
|           12 |        121 | 1980.00 | 165.000000 | 2026-11-02 | 2026-11-05 |
+--------------+------------+---------+------------+------------+------------+

• COUNT(*) counts rows. COUNT(column) counts only non-NULL values in that column.
• SUM() and AVG() work on numbers, and here on the expression qty * unit_price.
• MIN() and MAX() also work on dates and text.

Wrap AVG() in ROUND(AVG(...), 2) if you want fewer decimal places in your report.

Step 3: GROUP BY One Column

Now split the rows into groups, one group per branch, and run the aggregates on each group:

+-------------+--------------+---------+
| branch      | transactions | revenue |
+-------------+--------------+---------+
| Quezon City |            4 |  843.00 |
| Makati      |            5 |  612.00 |
| Pasig       |            3 |  525.00 |
+-------------+--------------+---------+

Read it as: "for each branch, count the sales and add up the revenue." Always add ORDER BY when the order matters. GROUP BY alone does not promise any order in MySQL 8.

Step 4: GROUP BY Multiple Columns

List more than one column to get one row for every unique combination, here every cashier in every branch:

+-------------+---------+--------------+---------+
| branch      | cashier | transactions | revenue |
+-------------+---------+--------------+---------+
| Makati      | Liza    |            3 |  370.00 |
| Makati      | Jon     |            2 |  242.00 |
| Pasig       | Ana     |            3 |  525.00 |
| Quezon City | Rico    |            2 |  480.00 |
| Quezon City | Mae     |            2 |  363.00 |
+-------------+---------+--------------+---------+

Step 5: GROUP BY with JOIN

The category lives in the products table, so join first and then group. This is the most common pattern in real reports:

+---------------+------------+---------+
| category      | items_sold | revenue |
+---------------+------------+---------+
| Beverages     |         55 |  820.00 |
| Food          |         31 |  695.00 |
| Personal Care |         35 |  465.00 |
+---------------+------------+---------+

If JOIN is still new to you, read our SQL JOINs tutorial first.

Step 6: Filter Groups with HAVING

WHERE filters rows before grouping. HAVING filters groups after grouping, so it can use aggregate results. Here we keep only branches with more than 600 in revenue:

+-------------+---------+
| branch      | revenue |
+-------------+---------+
| Quezon City |  843.00 |
| Makati      |  612.00 |
+-------------+---------+

Pasig (525.00) was removed because its group total did not pass the HAVING condition.

Step 7: WHERE and HAVING Together

You can use both in one query. This one keeps only beverage rows (WHERE), totals them per branch, then keeps branches with at least 200 in beverage sales (HAVING):

+-------------+------------------+
| branch      | beverage_revenue |
+-------------+------------------+
| Quezon City |           480.00 |
| Makati      |           280.00 |
+-------------+------------------+

Pasig sold only 60.00 worth of beverages, so it is filtered out. Notice that MySQL and MariaDB let you use the alias beverage_revenue inside HAVING. That is convenient, but some other databases, such as PostgreSQL and SQL Server, require you to repeat the expression.

Step 8: COUNT(DISTINCT) for Unique Values

To count unique values inside each group, use COUNT(DISTINCT column):

+------------+--------------+------------------+----------+
| sale_date  | transactions | cashiers_on_duty | branches |
+------------+--------------+------------------+----------+
| 2026-11-02 |            3 |                2 |        2 |
| 2026-11-03 |            3 |                3 |        2 |
| 2026-11-04 |            3 |                2 |        2 |
| 2026-11-05 |            3 |                3 |        3 |
+------------+--------------+------------------+----------+

Every day had three transactions, but the number of different cashiers and branches changes from day to day.

Step 9: Pivot-Style Report with SUM(CASE ...)

Managers often want categories as columns instead of rows. Conditional aggregation does that without any special pivot feature:

+-------------+-----------+--------+---------------+--------+
| branch      | beverages | food   | personal_care | total  |
+-------------+-----------+--------+---------------+--------+
| Quezon City |    480.00 | 228.00 |        135.00 | 843.00 |
| Makati      |    280.00 | 242.00 |         90.00 | 612.00 |
| Pasig       |     60.00 | 225.00 |        240.00 | 525.00 |
+-------------+-----------+--------+---------------+--------+

Each CASE returns the sale amount only for one category and 0 for everything else, so each SUM becomes a separate column. The same trick works for "paid vs unpaid", "passed vs failed" or monthly columns.

Step 10: Subtotals and Grand Total with WITH ROLLUP

WITH ROLLUP adds extra summary rows: a subtotal for each branch and a grand total at the end. The subtotal rows show NULL in the columns being rolled up:

+-------------+---------+---------+
| branch      | cashier | revenue |
+-------------+---------+---------+
| Makati      | Jon     |  242.00 |
| Makati      | Liza    |  370.00 |
| Makati      | NULL    |  612.00 |
| Pasig       | Ana     |  525.00 |
| Pasig       | NULL    |  525.00 |
| Quezon City | Mae     |  363.00 |
| Quezon City | Rico    |  480.00 |
| Quezon City | NULL    |  843.00 |
| NULL        | NULL    | 1980.00 |
+-------------+---------+---------+

The rows with cashier = NULL are branch subtotals, and the last row is the grand total of 1980.00, which matches Step 2. In MySQL 8 you can use the GROUPING() function to label those rows, for example as "All cashiers".

Bonus: Top 3 Best-Selling Products

+-----------------+------------+
| name            | items_sold |
+-----------------+------------+
| Coffee 3-in-1   |         35 |
| Shampoo Sachet  |         30 |
| Instant Noodles |         21 |
+-----------------+------------+

Group by the product id and name so two products with the same name are never merged by accident.

Common Errors and How to Fix Them

1. "Invalid use of group function"

This happens when you put an aggregate inside WHERE:

ERROR 1111 (HY000) at line 1: Invalid use of group function

Fix: move the condition to HAVING, as in Step 6. WHERE runs before the groups exist, so it cannot know a group's total.

2. "isn't in GROUP BY" (ONLY_FULL_GROUP_BY)

MySQL 5.7 and 8.x enable ONLY_FULL_GROUP_BY by default. In that mode, every selected column must be either in the GROUP BY list or inside an aggregate. To reproduce it on MariaDB, we turned the mode on for the session first:

ERROR 1055 (42000) at line 2: 'store_reports.sales.cashier' isn't in GROUP BY

A branch has several cashiers, so the database cannot know which one to show. On MySQL the message is longer and mentions error 1055 and only_full_group_by, but the meaning is the same. Fix: add the column to GROUP BY (as in Step 4), wrap it in an aggregate such as MAX(cashier) or GROUP_CONCAT(DISTINCT cashier), or remove it. Turning the mode off hides the problem and can give you random values, so fix the query instead.

Quick Reference: Order of a GROUP BY Query

SELECT   columns, aggregates
FROM     table
JOIN     other tables
WHERE    row filter (before grouping)
GROUP BY grouping columns
HAVING   group filter (after grouping)
ORDER BY sort
LIMIT    number of rows

Conclusion

With GROUP BY and a handful of aggregate functions you can build almost every summary a dashboard needs: totals per branch, counts per day, top products and category breakdowns. Remember the key rule: WHERE filters rows, HAVING filters groups, and every non-aggregated column in SELECT belongs in GROUP BY. Download the SQL files below, run them on your own MySQL or MariaDB server, and try writing a "sales per day per branch" report as practice. Happy coding!