Curriculum
GROUP BY and HAVING are among the most powerful SQL concepts used in Data Analytics, Business Intelligence, Reporting, and Database Management. While Aggregate Functions such as COUNT(), SUM(), AVG(), MAX(), and MIN() summarize data, GROUP BY and HAVING allow analysts to organize data into meaningful categories and apply conditions to summarized results.
Organizations generate massive datasets containing sales transactions, customer information, employee records, and financial data. Simply calculating totals is often not enough. Businesses need insights such as:
GROUP BY and HAVING make these types of analyses possible.
Organizations use GROUP BY and HAVING to:
Mastering GROUP BY and HAVING is essential for Data Analysts, Business Analysts, Database Administrators, and Business Intelligence professionals.
GROUP BY is used to organize records into groups based on one or more columns.
Once records are grouped, Aggregate Functions can be applied to each group.
Example:
Sales Table
| Region | Revenue |
|---|---|
| North | 50000 |
| North | 30000 |
| South | 70000 |
| South | 20000 |
Instead of calculating total revenue for the entire table, GROUP BY allows revenue to be calculated separately for each region.
Benefits:
Businesses rarely analyze entire datasets as a single unit.
Examples:
GROUP BY enables these analyses.
Benefits:
SELECT column_name,
aggregate_function(column_name)
FROM table_name
GROUP BY column_name;This structure groups records and applies an aggregate function.
Table:
| Region | Revenue |
|---|---|
| North | 50000 |
| North | 30000 |
| South | 70000 |
| South | 20000 |
Query:
SELECT Region,
SUM(Revenue) AS TotalRevenue
FROM Sales
GROUP BY Region;Result:
| Region | TotalRevenue |
|---|---|
| North | 80000 |
| South | 90000 |
This provides a regional sales summary.
Example:
SELECT City,
COUNT(*) AS CustomerCount
FROM Customers
GROUP BY City;Result:
| City | CustomerCount |
|---|---|
| Jaipur | 150 |
| Delhi | 200 |
| Mumbai | 100 |
Applications:
Example:
SELECT Department,
AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;Result:
Average salary for each department.
Applications:
Example:
SELECT Department,
MAX(Salary) AS HighestSalary
FROM Employees
GROUP BY Department;Applications:
Example:
SELECT Department,
MIN(Salary) AS LowestSalary
FROM Employees
GROUP BY Department;Applications:
SQL allows grouping by multiple columns.
Example:
SELECT Region,
ProductCategory,
SUM(Revenue) AS TotalRevenue
FROM Sales
GROUP BY Region,
ProductCategory;Result:
Revenue by Region and Product Category.
Applications:
HAVING filters grouped data.
WHERE filters rows before grouping.
HAVING filters groups after grouping.
This distinction is extremely important.
Consider:
Revenue by Region
Management wants only regions with revenue above ₹100,000.
GROUP BY creates the summary.
HAVING filters the summarized results.
Benefits:
SELECT column_name,
aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;HAVING is always used after GROUP BY.
Query:
SELECT Region,
SUM(Revenue) AS TotalRevenue
FROM Sales
GROUP BY Region
HAVING SUM(Revenue) > 80000;Result:
Only regions exceeding ₹80,000 revenue are displayed.
Applications:
SELECT Department,
COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 50;Result:
Only departments with more than 50 employees.
Applications:
This is one of the most important SQL concepts.
Filters individual rows.
Example:
SELECT *
FROM Sales
WHERE Revenue > 50000;Filters rows before grouping.
Filters grouped results.
Example:
SELECT Region,
SUM(Revenue)
FROM Sales
GROUP BY Region
HAVING SUM(Revenue) > 100000;Filters groups after aggregation.
Both can be used together.
Example:
SELECT Region,
SUM(Revenue) AS TotalRevenue
FROM Sales
WHERE Revenue > 10000
GROUP BY Region
HAVING SUM(Revenue) > 50000;Process:
Benefits:
Powerful analytical reporting.
Example:
SELECT ProductCategory,
SUM(Revenue) AS TotalRevenue
FROM Sales
GROUP BY ProductCategory;Applications:
Benefits:
Business visibility.
Example:
SELECT City,
COUNT(*) AS CustomerCount
FROM Customers
GROUP BY City
HAVING COUNT(*) > 100;Applications:
Benefits:
Geographic insights.
Example:
SELECT Department,
AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;Applications:
Benefits:
HR decision support.
Example:
SELECT Month,
SUM(Revenue) AS MonthlyRevenue
FROM Finance
GROUP BY Month;Applications:
Benefits:
Performance tracking.
Business Intelligence systems use GROUP BY and HAVING extensively for:
Examples:
These metrics power business decisions.
May reduce performance.
Causes SQL errors.
Incorrect:
SELECT Region,
ProductName,
SUM(Revenue)
FROM Sales
GROUP BY Region;ProductName is neither grouped nor aggregated.
Produces incorrect summaries.
Incorrect:
WHERE SUM(Revenue) > 50000Use HAVING instead.
Understanding these mistakes improves query accuracy.
Improve business insights.
Enhance report readability.
Improve performance.
Maintain query correctness.
Ensure analytical accuracy.
Improve reliability.
These practices support professional SQL reporting.
Data Analysts use GROUP BY and HAVING for:
These commands are essential for analytical reporting.
Organizations benefit through:
GROUP BY and HAVING transform raw datasets into meaningful business information.
After completing this lesson, you will be able to:
GROUP BY organizes records into groups and applies aggregate calculations to each group.
HAVING filters grouped results after aggregation.
WHERE filters rows before grouping, while HAVING filters groups after aggregation.
Yes. Multiple columns can be grouped together.
HAVING allows filtering based on summarized values such as SUM, COUNT, and AVG.
No. GROUP BY can be used without HAVING.
Yes. They are often combined in analytical queries.
They help summarize data, calculate KPIs, and generate meaningful business insights.
Want to master SQL, reporting, and Data Analytics?