TCS asked me this question in the interview, when working with aggregated data in SQL, two clauses often used together are GROUP BY and HAVING. Although they appear in similar contexts, they serve very different purposes. Understanding how they interact is a common interview expectation for mid-level SQL developers.
What Does GROUP BY Do?
The GROUP BY clause is used to combine rows that share common values into aggregated result sets.
It essentially tells SQL:
“Create a summary for each unique value (or combination of values) in these columns.”
For example, grouping by ProductCategory lets you calculate total sales for each category across all orders.
What Does HAVING Do?
The HAVING clause filters the output after grouping has taken place.
Unlike WHERE, which filters individual rows before aggregation, HAVING filters aggregated results such as:
SUM(OrderTotal)COUNT(CustomerID)AVG(OrderTotal)MAX()/MIN()
In short:
WHEREfilters rows →GROUP BYgroups rows →HAVINGfilters groups
Example Table
Consider the following Orders table:
| Column Name | Description |
|---|---|
CustomerID | Unique identifier for each customer |
ProductCategory | e.g., “Electronics”, “Books”, “Clothing” |
OrderTotal | Total amount spent in a single order |
Putting It All Together: Query Using Both GROUP BY and HAVING
SELECT
ProductCategory,
COUNT(CustomerID) AS TotalCustomers,
SUM(OrderTotal) AS TotalRevenue
FROM
Orders
-- GROUP BY: Creates one summary row per category
GROUP BY
ProductCategory
-- HAVING: Removes groups that don’t meet the required thresholds
HAVING
SUM(OrderTotal) > 50000
AND COUNT(CustomerID) > 100;
What This Query Does
- Groups all orders by product category
- Calculates how many customers bought from each category
- Calculates the category’s total revenue
- Filters out categories that have:
- Less than ₹50,000 total revenue
- Fewer than 100 customers
Follow-Up Question: When Should You Use WHERE Instead of HAVING?
Use WHERE when you want to filter individual rows before aggregation takes place.
Use HAVING only when you need to filter results after the aggregation.
Examples:
| Use Case | Clause |
|---|---|
| Remove orders below ₹100 before calculating totals | WHERE OrderTotal > 100 |
| Show only categories with revenue above ₹50,000 | HAVING SUM(OrderTotal) > 50000 |
Summary
GROUP BY→ Organizes rows into groupsHAVING→ Filters those groups based on aggregate resultsWHERE→ Filters rows before grouping happens
Understanding these three clauses together is essential for writing efficient and readable SQL queries.
Also Read: Hierarchical Queries in SQL: A Complete Beginner-to-Advanced
