SQL GROUP BY, HAVING

When working with databases, we often need to group data and apply calculations like totals, averages, or counts. In SQL, this is done using the GROUP BY clause. Sometimes, after grouping, we want to filter the grouped results — that’s where HAVING comes in.

Let’s break this down step-by-step.

---

1. What is GROUP BY in SQL?

The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It is usually used with aggregate functions like:

• COUNT() – counts the number of rows
• SUM() – adds values
• AVG() – calculates the average
• MAX() – finds the highest value
• MIN() – finds the lowest value

Syntax:

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

Key Points:

• It groups rows based on one or more columns.
• It is always used after the WHERE clause and before ORDER BY.
• It works with aggregate functions to produce summarized results.

---

Example:

Suppose we have a table Orders:

OrderID	Customer	Amount
1	John	250
2	Emma	300
3	John	150
4	Emma	200
5	Alex	500

Query:

SELECT Customer, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY Customer;

Result:

Customer	TotalAmount
John	400
Emma	500
Alex	500

Here:

• We grouped the data by Customer.
• SUM(Amount) added up the orders for each customer.

---

2. What is HAVING in SQL?

The HAVING clause filters the results of groups created by GROUP BY.
While WHERE filters rows before grouping, HAVING filters after grouping.

Syntax:

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

Key Points:

• You cannot use aggregate functions directly with WHERE.
• Use HAVING to filter grouped results based on aggregate values.

---

Example:

Continuing with the same Orders table.

Query:

SELECT Customer, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY Customer
HAVING SUM(Amount) > 400;

Result:

Customer	TotalAmount
Emma	500
Alex	500

Here:

• We grouped the data by Customer.
• Then used HAVING to show only customers whose total amount is more than 400.

---

3. Difference Between WHERE and HAVING

Feature	WHERE	HAVING
Filters rows before grouping	✅	❌
Filters groups after grouping	❌	✅
Can use aggregate functions	❌	✅
Used with GROUP BY	Optional	Yes, usually

Example:

 Filter before grouping
SELECT Customer, SUM(Amount)
FROM Orders
WHERE Amount > 200
GROUP BY Customer;

-- Filter after grouping
SELECT Customer, SUM(Amount)
FROM Orders
GROUP BY Customer
HAVING SUM(Amount) > 400;

---

4. Using Multiple Columns with GROUP BY

You can group by more than one column.

Example:

SELECT Customer, OrderID, SUM(Amount)
FROM Orders
GROUP BY Customer, OrderID;

This groups rows by Customer and OrderID together.

---

5. Practical Example: Sales Report

Let’s say you have a Sales table:

Product	Region	Sales
Phone	East	200
Phone	West	150
Laptop	East	300
Laptop	West	250
Phone	East	100

Query to find total sales per product per region where sales exceed 250:

SELECT Product, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY Product, Region
HAVING SUM(Sales) > 250;

Result:

Product	Region	TotalSales
Laptop	East	300

---

6. Summary

• GROUP BY → Groups rows based on column values.
• HAVING → Filters grouped results (works with aggregates).
• WHERE filters before grouping, HAVING filters after grouping.
• Often used together to create powerful summaries in SQL.

---

Example Putting It All Together:

SELECT Customer, COUNT(OrderID) AS OrdersCount, SUM(Amount) AS TotalAmount
FROM Orders
WHERE Amount > 100
GROUP BY Customer
HAVING SUM(Amount) > 300
ORDER BY TotalAmount DESC;

This query:

1. Filters orders above 100 (WHERE).
2. Groups by customer (GROUP BY).
3. Keeps only customers with total purchases over 300 (HAVING).
4. Sorts results in descending order.

---

Final Tip:
Think of GROUP BY as creating “buckets” of similar data, and HAVING as a filter that decides which buckets to keep.