SQL GROUP BY Explained With Real Sales Data

What You Will Learn

• What GROUP BY does and why it is so important
• How to group by one column and by multiple columns
• The HAVING clause — filtering groups (not rows)
• The exact order SQL evaluates clauses

Why This Topic Matters

GROUP BY is the SQL version of an Excel pivot table (lesson 15) or a pandas groupby() (lesson 26). It is the foundation of every business report: "revenue by city", "orders by month", "top customers by spend". If you only learn one intermediate SQL feature, make it this one.

Sample Sales Data

A more realistic sales table with 12 rows:

The Question GROUP BY Answers

Without GROUP BY, your aggregations collapse the whole table into one number: SUM(sales) returns ₹2,73,000 — total of everything. Useful, but limited.

With GROUP BY, you can ask: "What is the SUM of sales for each region separately?" Or: "For each product, what is the total sales?"

Your First GROUP BY

SELECT region, SUM(sales) AS total_sales
FROM sales
GROUP BY region;

Result:

What the database actually does

1. Sorts the rows by region — North rows together, South rows together.
2. For each group, computes SUM(sales).
3. Returns one row per group.

The output has 2 rows because there are 2 unique regions.

The Rule: Every Non-Aggregated Column Must Be in GROUP BY

This is the rule that catches every beginner:

If a column appears in SELECT and is not wrapped in an aggregate function (SUM, COUNT, AVG, etc.), it must appear in GROUP BY.

So this is invalid:

SELECT region, product, SUM(sales)   -- product is not aggregated
FROM sales
GROUP BY region;                       -- and not in GROUP BY either

Most databases reject it. (SQLite allows it but returns an arbitrary product for each region — silently wrong.) The fix is to add product to GROUP BY:

GROUP BY Multiple Columns

SELECT region, product, SUM(sales) AS total_sales
FROM sales
GROUP BY region, product
ORDER BY region, total_sales DESC;

Result:

Now you see sales broken down by region AND product. The result has 6 rows because there are 2 regions × 3 products = 6 combinations (and all 6 actually appear in the data).

Grouping by Date Parts

Grouping by raw dates gives one row per day. To roll up to months, use date functions:

-- PostgreSQL / SQLite (strftime):
SELECT strftime('%Y-%m', date) AS month, SUM(sales) AS monthly_sales
FROM sales
GROUP BY strftime('%Y-%m', date)
ORDER BY month;

-- MySQL:
SELECT DATE_FORMAT(date, '%Y-%m') AS month, SUM(sales)
FROM sales
GROUP BY DATE_FORMAT(date, '%Y-%m');

-- SQL Server:
SELECT FORMAT(date, 'yyyy-MM') AS month, SUM(sales)
FROM sales
GROUP BY FORMAT(date, 'yyyy-MM');

HAVING — Filtering Groups

WHERE filters rows before grouping. HAVING filters groups after grouping.

SELECT product, SUM(sales) AS total_sales
FROM sales
GROUP BY product
HAVING SUM(sales) > 50000;

Result:

Only Laptop had total sales above ₹50,000. Keyboard (₹4,500) and Mouse (₹2,000) are filtered out.

WHERE vs HAVING

Combining both

SELECT product, SUM(sales) AS total_sales
FROM sales
WHERE region = 'South'      -- only South rows enter the grouping
GROUP BY product
HAVING SUM(sales) > 1000;    -- keep only products with > 1000 in sales

The Full Clause Order

SQL evaluates clauses in this order (even though you write them differently):

1. FROM — load the source table(s)
2. WHERE — filter rows
3. GROUP BY — group surviving rows
4. HAVING — filter groups
5. SELECT — pick columns / compute aggregates
6. ORDER BY — sort the result
7. LIMIT — pick top N

Memorize this order. Most SQL bugs trace back to misunderstanding it — e.g., trying to use a column alias from SELECT inside WHERE (not allowed, because WHERE runs before SELECT).

Common Mistakes

1. Selecting a column not in GROUP BY. Violates the rule above. Add it to GROUP BY or wrap it in an aggregate.
2. Using WHERE to filter on an aggregate. WHERE SUM(sales) > 1000 is invalid. Use HAVING.
3. Forgetting that GROUP BY changes row count. Output rows = number of groups, not number of input rows. A common surprise.
4. Assuming GROUP BY sorts the output. It does not, formally. Some databases sort by the GROUP BY columns as a side effect, but you should always add ORDER BY explicitly.
5. Counting the wrong thing. SELECT product, COUNT(*) counts rows per product. SELECT product, COUNT(sales) counts non-NULL sales values per product. Usually you want COUNT(*).

Practical Exercise (15 minutes)

Using the 12-row sales table above, write queries to compute:

1. Total sales by region.
2. Total sales by product.
3. Number of orders by region.
4. Total sales by region AND product.
5. Products whose total sales exceed ₹50,000.
6. Regions where the average sale is above ₹20,000.
7. Top product by sales in the South region.

Mini Challenge

Write a query that returns, for each region, the product with the highest total sales. (This actually requires a subquery or window function — see lesson 23. For now, see how far you can get with just GROUP BY and ORDER BY. Can you at least produce one row per region that shows the top product? Hint: use a subquery per region.)

Key Takeaways