GROUP BY & HAVING Interview Questions

Master SQL GROUP BY and HAVING with interview-focused questions covering aggregate functions, grouping, filtering groups, ROLLUP, CUBE, GROUPING SETS, execution order, optimization, and enterprise best practices.

Introduction

Enterprise applications often need summarized information rather than individual records.

Examples include

Total Sales
Average Salary
Customer Count
Department-wise Employees
Monthly Revenue
Product Statistics

SQL provides

GROUP BY
HAVING

to aggregate and analyze data efficiently.

These topics are frequently asked in SQL interviews because they demonstrate an understanding of reporting and analytics.

Aggregation Architecture

flowchart LR

Table --> GroupBy["GROUP BY"]

GroupBy["GROUP BY"] --> AggregateFunctions["Aggregate Functions"]

AggregateFunctions["Aggregate Functions"] --> HAVING

HAVING --> Result

Sample Employee Table

ID	Name	Department	Salary
101	John	IT	90000
102	Alice	HR	80000
103	David	IT	95000
104	Bob	Finance	85000
105	Emma	HR	70000

1. What is GROUP BY?

Answer

GROUP BY groups rows having the same values into one summary row.

It is commonly used with aggregate functions.

Example

SELECT department,
       COUNT(*)
FROM Employee
GROUP BY department;

GROUP BY Workflow

Employee Records

↓

GROUP BY Department

↓

IT

↓

HR

↓

Finance

2. Why is GROUP BY used?

GROUP BY helps generate

Reports
Dashboards
Analytics
Business Summaries

instead of returning every row.

3. Which aggregate functions are commonly used?

Most common

COUNT()
SUM()
AVG()
MIN()
MAX()

Aggregate Functions

GROUP

↓

COUNT

SUM

AVG

MIN

MAX

4. What does COUNT() do?

COUNT()

returns

the number of rows.

Example

SELECT COUNT(*)
FROM Employee;

5. Difference between COUNT(*) and COUNT(column)?

COUNT(*)

Counts

all rows.

COUNT(column)

Counts

only non-NULL values.

Example

SELECT COUNT(phone)
FROM Employee;

NULL phone numbers

are ignored.

6. What does SUM() do?

Returns

the total value.

Example

SELECT SUM(salary)
FROM Employee;

7. What does AVG() do?

Returns

the average value.

Example

SELECT AVG(salary)
FROM Employee;

8. What does MIN() do?

Returns

the smallest value.

SELECT MIN(salary)
FROM Employee;

9. What does MAX() do?

Returns

the largest value.

SELECT MAX(salary)
FROM Employee;

10. GROUP BY Example

SELECT department,
       COUNT(*)
FROM Employee
GROUP BY department;

Result

Department	Employees
IT	2
HR	2
Finance	1

11. Can GROUP BY use multiple columns?

Yes.

SELECT department,
       city,
       COUNT(*)
FROM Employee
GROUP BY department,
         city;

Multiple Column GROUP BY

Department

↓

City

↓

Employee Count

12. What is HAVING?

HAVING filters

groups

after aggregation.

HAVING Example

SELECT department,
       COUNT(*)
FROM Employee
GROUP BY department
HAVING COUNT(*) > 1;

HAVING Workflow

flowchart LR

Rows --> GroupBy["GROUP BY"]

GroupBy["GROUP BY"] --> Aggregate

Aggregate --> HAVING

HAVING --> Result

13. Difference between WHERE and HAVING?

WHERE	HAVING
Filters Rows	Filters Groups
Before GROUP BY	After GROUP BY
Cannot use aggregates	Can use aggregates

14. Can HAVING be used without GROUP BY?

Yes.

Some databases allow HAVING without GROUP BY,

treating the entire result as a single group.

Example

SELECT COUNT(*)
FROM Employee
HAVING COUNT(*) > 0;

15. What is the execution order?

Logical order

FROM

↓

WHERE

↓

GROUP BY

↓

HAVING

↓

SELECT

↓

ORDER BY

16. What is ROLLUP?

ROLLUP generates

subtotals

and

grand totals.

ROLLUP Example

SELECT department,
       SUM(salary)
FROM Employee
GROUP BY ROLLUP(department);

ROLLUP Result

Department	Total Salary
IT	185000
HR	150000
Finance	85000
NULL	420000

NULL represents

the grand total.

17. What is CUBE?

CUBE generates

all possible grouping combinations.

Useful for

OLAP reports.

CUBE Example

GROUP BY CUBE(department, city)

18. What are GROUPING SETS?

GROUPING SETS allow

multiple grouping combinations

within one query.

Example

GROUP BY GROUPING SETS
(
(department),
(city),
(department, city)
)

19. Banking Example

Department-wise deposits

SELECT branch,
SUM(balance)
FROM Accounts
GROUP BY branch;

20. E-Commerce Example

Monthly sales

SELECT month,
SUM(total)
FROM Orders
GROUP BY month;

21. HR Example

Average salary

SELECT department,
AVG(salary)
FROM Employee
GROUP BY department;

22. Healthcare Example

Patients by blood group

SELECT blood_group,
COUNT(*)
FROM Patient
GROUP BY blood_group;

23. Social Media Example

Posts per user

SELECT user_id,
COUNT(*)
FROM Posts
GROUP BY user_id;

24. Analytics Example

Top selling categories

SELECT category,
SUM(quantity)
FROM Sales
GROUP BY category;

25. Production Example

Daily dashboard

SELECT status,
COUNT(*)
FROM Orders
GROUP BY status;

Displays

Pending Orders
Completed Orders
Cancelled Orders

26. Common Mistakes

Missing GROUP BY columns
Using aggregate functions inside WHERE
Confusing WHERE and HAVING
Selecting non-grouped columns
Forgetting NULL handling

27. Performance Tips

Group indexed columns when possible.
Filter using WHERE before GROUP BY.
Aggregate only required columns.
Avoid unnecessary GROUP BY.
Review execution plans.
Pre-aggregate frequently used reports where appropriate.

28. GROUP BY vs DISTINCT

GROUP BY	DISTINCT
Groups Data	Removes Duplicates
Supports Aggregates	No Aggregation
Used for Reports	Used for Unique Values

29. Best Practices

Use WHERE before GROUP BY.
Use HAVING only for aggregate filtering.
Index grouping columns.
Avoid grouping unnecessary columns.
Use aliases for readability.
Return only required aggregates.
Test performance on large datasets.
Keep queries simple.
Review execution plans.
Document complex reports.

30. Real-World Query Workflow

flowchart LR

FROM --> WhereGroupByAggregate["WHERE --> GROUP BY --> Aggregate --> HAVING --> SELECT --> OrderBy["ORDER BY"]"]

Enterprise Best Practices

Apply WHERE before aggregation to reduce processed rows.
Group only the columns required by the business.
Create indexes on frequently grouped columns.
Use HAVING only when filtering aggregated results.
Use ROLLUP or CUBE for reporting workloads.
Use materialized views for expensive aggregation queries.
Monitor execution plans for full table scans.
Avoid unnecessary sorting after aggregation.
Cache frequently requested reports.
Validate aggregation accuracy using production-like datasets.

Quick Revision

Topic	Key Point
GROUP BY	Groups Rows
HAVING	Filters Groups
COUNT	Number of Rows
SUM	Total Value
AVG	Average Value
MIN	Smallest Value
MAX	Largest Value
ROLLUP	Subtotals + Grand Total
CUBE	All Combinations
GROUPING SETS	Multiple Groupings

Interview Tips

Interviewers frequently ask

What is GROUP BY?
Why is GROUP BY used?
Difference between WHERE and HAVING.
COUNT(*) vs COUNT(column).
Explain aggregate functions.
GROUP BY vs DISTINCT.
What is ROLLUP?
What is CUBE?
What are GROUPING SETS?
How do you optimize GROUP BY queries?

A strong interview explanation is:

"GROUP BY groups rows with the same values into summary records, while aggregate functions such as COUNT, SUM, AVG, MIN, and MAX calculate statistics for each group. WHERE filters rows before grouping, whereas HAVING filters groups after aggregation. Advanced features like ROLLUP, CUBE, and GROUPING SETS support multidimensional reporting and analytics. In production systems, applying WHERE early, indexing grouping columns, and reviewing execution plans are essential for efficient aggregation queries."

Summary

GROUP BY and HAVING are fundamental SQL features used to summarize, analyze, and report business data. Combined with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX, they enable powerful analytical queries. Advanced capabilities including ROLLUP, CUBE, and GROUPING SETS make SQL suitable for enterprise reporting and data warehousing.

Mastering grouping, aggregation, filtering, and optimization techniques is essential for Backend Developers, Database Engineers, Data Engineers, Java Developers, and Solution Architects working with enterprise relational databases.