SQL GROUP BY and HAVING Clauses Guide With Examples

Structured Query Language (SQL) is one of the most powerful tools to handle and analyze data. Among the most useful features in SQL for performing data aggregation and analysis are the GROUP BY and HAVING clauses. These two clauses help summarize large datasets and extract meaningful insights — for example, finding total sales per region, counting students per class, or filtering groups based on certain conditions.

In this article, we’ll understand everything about GROUP BY and HAVING — from basic concepts to real-world examples.

🔹 1. Understanding the Need for GROUP BY

Imagine you have a table named Sales that contains the following columns:

Now, suppose you want to know the total quantity sold for each category.
You could write a query like this:

SELECT Category, SUM(Quantity)

FROM Sales

GROUP BY Category;

🔸 Output:

✅ Explanation:

• The GROUP BY clause groups rows that have the same values in the Category column.
• The SUM(Quantity) function adds up the quantity of all products within each category.
• Each group returns one row per unique category.

🔹 2. GROUP BY Syntax

The basic syntax of GROUP BY is:

SELECT column1, aggregate_function(column2)

FROM table_name

WHERE condition

GROUP BY column1;

Common aggregate functions used with GROUP BY are:

• SUM() → adds up numeric values
• AVG() → finds the average value
• COUNT() → counts rows
• MIN() → finds the smallest value
• MAX() → finds the largest value

🔹 3. Example 1 – Counting Employees per Department

Let’s say we have an Employees table:

Now, to find the number of employees in each department, you can write:

SELECT Department, COUNT(EmpID) AS TotalEmployees

FROM Employees

GROUP BY Department;

🔸 Output:

✅ The query groups the data by Department and counts how many employees belong to each department.

🔹 4. Example 2 – Finding Average Salary per Department

SELECT Department, AVG(Salary) AS AverageSalary

FROM Employees

GROUP BY Department;

🔸 Output:

🔹 5. Using GROUP BY with Multiple Columns

You can group by more than one column.
For example, suppose you have a Sales table that includes a Region column.

To find total sales by Product and Region, write:

SELECT Product, Region, SUM(SalesAmount) AS TotalSales

FROM Sales

GROUP BY Product, Region;

🔸 Output:

✅ Here, SQL first groups by Product and then by Region inside each product group.

🔹 6. GROUP BY with WHERE Clause

You can use WHERE before GROUP BY to filter rows before grouping.

Example: Get total sales for only the “Electronics” category.

SELECT Category, SUM(Quantity) AS TotalQuantity

FROM Sales

WHERE Category = 'Electronics'

GROUP BY Category;

✅ WHERE filters records before aggregation.

🔹 7. The HAVING Clause – Why Do We Need It?

The WHERE clause cannot be used to filter aggregated data (like SUM(), COUNT() etc.), because aggregation happens after filtering.

For example, this query will throw an error:

SELECT Department, SUM(Salary)

FROM Employees

WHERE SUM(Salary) > 80000   -- ❌ invalid

GROUP BY Department;

SQL first needs to group and calculate the total salary per department before checking the condition.
That’s where the HAVING clause comes in.

🔹 8. HAVING Clause Syntax

SELECT column1, aggregate_function(column2)

FROM table_name

GROUP BY column1

HAVING condition;

✅ The HAVING clause filters the groups created by GROUP BY, not individual rows.

🔹 9. Example 1 – Using HAVING to Filter Groups

Let’s return to departments where the total salary exceeds 80000:

SELECT Department, SUM(Salary) AS TotalSalary

FROM Employees

GROUP BY Department

HAVING SUM(Salary) > 80000;

🔸 Output:

✅ Explanation:

• First, the query groups the employees by Department.
• Then, it sums up the salary of each department.
• Finally, HAVING filters only those departments where total salary > 80000.

🔹 10. Example 2 – Combining WHERE and HAVING

You can use both WHERE and HAVING together.

Example: Show total sales for each category except “Clothing”, where total quantity sold is greater than 10.

SELECT Category, SUM(Quantity) AS TotalQuantity

FROM Sales

WHERE Category <> 'Clothing'

GROUP BY Category

HAVING SUM(Quantity) > 10;

✅ WHERE removes “Clothing” rows before grouping.
✅ HAVING filters only those groups that have SUM(Quantity) > 10.

🔹 11. Example 3 – HAVING with COUNT()

Find departments having more than 1 employee.

SELECT Department, COUNT(EmpID) AS TotalEmployees

FROM Employees

GROUP BY Department

HAVING COUNT(EmpID) > 1;

🔸 Output:

🔹 12. Key Difference Between WHERE and HAVING

🔹 13. Real-World Use Case Example

Suppose you manage an e-commerce database and want to find:

“All product categories that generated more than ₹1,00,000 in total sales, but only consider products sold more than 5 times.”

You can write:

SELECT Category, SUM(Price * Quantity) AS TotalRevenue

FROM Sales

WHERE Quantity > 5

GROUP BY Category

HAVING SUM(Price * Quantity) > 100000;

✅ This query is practical for analyzing profitable product categories.

🔹 14. Best Practices for Using GROUP BY and HAVING

1. Always use aggregate functions in the SELECT list when grouping.
2. Don’t use column aliases in the HAVING clause in MySQL (some databases allow it).
3. Use WHERE to filter rows early — it improves query performance.
4. Always check results with ORDER BY to view data in a clear order.
5. ORDER BY SUM(Salary) DESC;

🔹 15. Summary Table

Conclusion

The GROUP BY and HAVING clauses are essential when performing data aggregation in SQL.

• GROUP BY organizes your data into logical groups.
• HAVING filters those groups based on aggregate conditions.

When combined, they help you summarize data efficiently and make powerful reports — for example:

• Total sales per region
• Departments with more than 5 employees
• Products generating high revenue

These clauses are vital for real-world database analytics, especially in industries like e-commerce, banking, HR, and education.

Final Tip

Next time you work on SQL queries, think in terms of “groups” and “conditions on groups.” That’s the real power of combining GROUP BY and HAVING in SQL.

Do visit our channel to learn More: SevenMentor

Author:-

Aarti Choudhary