SQL GROUP BY and HAVING with Real Business Examples

SQL GROUP BY and HAVING with real business examples

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 BY turns many rows into one row per group, and aggregate functions (SUM, COUNT, AVG, MIN, MAX) calculate a value for each group. WHERE filters rows before grouping; HAVING filters groups after grouping, so it can use aggregates like HAVING SUM(amount) > 10000.

The example table: orders

order_idcustomerregioncategoryorder_dateamount
1AshaNorthLaptop2026-10-0255000
2RaviWestMobile2026-10-0318000
3AshaNorthMobile2026-10-1022000
4MeenaSouthLaptop2026-10-1561000
5RaviWestAccessory2026-11-012500
6ArjunNorthAccessory2026-11-041800

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;
regiontotal_sales
North78800
West20500
South61000

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

WHEREHAVING
FiltersIndividual rowsGroups
RunsBefore GROUP BYAfter GROUP BY
Can use SUM, COUNT, AVGNoYes
ExampleWHERE 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

  1. FROM / JOIN: get the tables
  2. WHERE: filter rows
  3. GROUP BY: make groups
  4. HAVING: filter groups
  5. SELECT: calculate output columns
  6. ORDER BY: sort
  7. 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.

Tags

Post a Comment

0 Comments
* Please Don't Spam Here. All the Comments are Reviewed by Admin.
Learn Excel, Power BI, and Finance in Simple Hindi and English Follow step-by-step tutorials and practical examples designed for students, job seekers, and working professionals. We cover Power BI dashboards, DAX formulas, Excel tricks, financial literacy, and AI tools.