Raw tables have one row per order, but business questions are about totals: sales per region, orders per customer, average price per category. In SQL you answer them with GROUP BY, and you filter those totals with HAVING.
This guide explains both with a simple orders table and real business questions, shows the difference between WHERE and HAVING, and covers the mistakes that cause errors or wrong numbers.
Quick answer:GROUP BYturns many rows into one row per group, and aggregate functions (SUM, COUNT, AVG, MIN, MAX) calculate a value for each group.WHEREfilters rows before grouping;HAVINGfilters groups after grouping, so it can use aggregates likeHAVING SUM(amount) > 10000.
The example table: orders
| order_id | customer | region | category | order_date | amount |
|---|---|---|---|---|---|
| 1 | Asha | North | Laptop | 2026-10-02 | 55000 |
| 2 | Ravi | West | Mobile | 2026-10-03 | 18000 |
| 3 | Asha | North | Mobile | 2026-10-10 | 22000 |
| 4 | Meena | South | Laptop | 2026-10-15 | 61000 |
| 5 | Ravi | West | Accessory | 2026-11-01 | 2500 |
| 6 | Arjun | North | Accessory | 2026-11-04 | 1800 |
GROUP BY syntax
SELECT group_column, AGGREGATE(value_column)
FROM table_name
WHERE row_condition
GROUP BY group_column
HAVING group_condition
ORDER BY ...;
1. Total sales per region
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region;
| region | total_sales |
|---|---|
| North | 78800 |
| West | 20500 |
| South | 61000 |
2. Count orders and customers per region
SELECT region,
COUNT(*) AS orders,
COUNT(DISTINCT customer) AS customers,
AVG(amount) AS avg_order_value
FROM orders
GROUP BY region;
COUNT(*) counts rows; COUNT(DISTINCT customer) counts unique customers. North has 3 orders from 2 customers.
3. Group by more than one column
SELECT region, category, SUM(amount) AS total_sales
FROM orders
GROUP BY region, category
ORDER BY region, total_sales DESC;
You get one row for each combination of region and category.
4. Monthly sales
-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) AS total_sales
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;
-- SQL Server
SELECT FORMAT(order_date, 'yyyy-MM') AS month, SUM(amount) AS total_sales
FROM orders
GROUP BY FORMAT(order_date, 'yyyy-MM')
ORDER BY month;
-- PostgreSQL
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
Date functions differ between databases; the GROUP BY idea is the same.
5. HAVING: filter groups
Question: which customers spent more than ₹20,000 in total?
SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
HAVING SUM(amount) > 20000;
Result: Asha (77,000), Meena (61,000) and Ravi (20,500). Arjun (1,800) is filtered out.
You cannot write WHERE SUM(amount) > 20000, because WHERE runs before the totals exist.
6. WHERE and HAVING together
Question: in October only, which regions had more than one order?
SELECT region, COUNT(*) AS orders
FROM orders
WHERE order_date >= '2026-10-01' AND order_date < '2026-11-01'
GROUP BY region
HAVING COUNT(*) > 1;
WHERE keeps only October rows; GROUP BY counts per region; HAVING keeps regions with more than one order. Result: North (2 orders).
WHERE vs HAVING
| WHERE | HAVING | |
|---|---|---|
| Filters | Individual rows | Groups |
| Runs | Before GROUP BY | After GROUP BY |
| Can use SUM, COUNT, AVG | No | Yes |
| Example | WHERE region = 'North' | HAVING SUM(amount) > 50000 |
Rule of thumb: filter rows with WHERE whenever possible. It reduces the data before grouping and is faster.
The order SQL runs your query
- FROM / JOIN: get the tables
- WHERE: filter rows
- GROUP BY: make groups
- HAVING: filter groups
- SELECT: calculate output columns
- ORDER BY: sort
- LIMIT / TOP: keep the first rows
This order explains most errors: for example, why a column alias defined in SELECT often cannot be used in WHERE.
Real business examples
Top 5 customers by revenue
SELECT customer, SUM(amount) AS revenue
FROM orders
GROUP BY customer
ORDER BY revenue DESC
LIMIT 5; -- SQL Server: SELECT TOP 5 ...
Categories with an average order above ₹10,000
SELECT category, AVG(amount) AS avg_order
FROM orders
GROUP BY category
HAVING AVG(amount) > 10000;
Find duplicate records
SELECT customer, order_date, amount, COUNT(*) AS copies
FROM orders
GROUP BY customer, order_date, amount
HAVING COUNT(*) > 1;
Repeat customers
SELECT customer, COUNT(*) AS orders
FROM orders
GROUP BY customer
HAVING COUNT(*) >= 2;
Common mistakes
"Column must appear in the GROUP BY clause"
Every column in SELECT must be either in GROUP BY or inside an aggregate. SELECT region, customer, SUM(amount) ... GROUP BY region fails because customer is neither.
Using WHERE with an aggregate
Move conditions on SUM, COUNT or AVG to HAVING.
COUNT(column) vs COUNT(*)
COUNT(column) ignores NULLs; COUNT(*) counts every row. Choose deliberately.
Totals too high after a JOIN
Joining a one-to-many table repeats rows before grouping. Aggregate the many side first, then join. See SQL JOINs explained.
Frequently asked questions
Can I use HAVING without GROUP BY?
Yes, but then the whole table is one group. It is rarely useful; use WHERE for row filters.
Can I GROUP BY a column alias?
MySQL and PostgreSQL allow it; SQL Server does not. Repeat the expression to be safe.
Is GROUP BY the same as a pivot table?
Very close. GROUP BY gives you the rows and values of a pivot table; turning categories into columns needs conditional aggregation or PIVOT.
Practice task
Using the orders table, write queries for: total sales by category; customers with 2 or more orders; and, for November only, regions with total sales above ₹2,000.
