Grouping Data
SQL Basics Course
Topic 11: GROUP BY and HAVING
Learn how to group records and generate category-based reports.
Learning Objectives
- Understand GROUP BY
- Group records into categories
- Use aggregate functions with groups
- Filter grouped data using HAVING
- Create real-world business reports
Why GROUP BY?
GROUP BY combines rows that have the same values into summary groups.
| Department | Employee |
|---|---|
| IT | John |
| IT | Sarah |
| HR | Mike |
Instead of viewing every employee individually, we can group them by department.
Basic GROUP BY
SELECT department
FROM employees
GROUP BY department;
This returns unique department groups.
GROUP BY with COUNT()
SELECT department,
COUNT(*) AS total_employees
FROM employees
GROUP BY department;
| Department | Total Employees |
|---|---|
| IT | 2 |
| HR | 1 |
GROUP BY with SUM()
SELECT department,
SUM(salary) AS total_salary
FROM employees
GROUP BY department;
This calculates total salary for each department.
GROUP BY with AVG()
SELECT department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;
Calculates average salary per department.
GROUP BY Multiple Columns
SELECT city, department,
COUNT(*)
FROM employees
GROUP BY city, department;
Groups records using more than one column.
What is HAVING?
HAVING filters grouped results.
SELECT department,
COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
Only departments with more than 5 employees are displayed.
WHERE vs HAVING
| WHERE | HAVING |
|---|---|
| Filters rows before grouping | Filters groups after grouping |
| Works with individual records | Works with aggregated results |
Real World Sales Report
SELECT city,
SUM(amount) AS total_sales
FROM orders
GROUP BY city;
| City | Total Sales |
|---|---|
| Delhi | 150000 |
| Mumbai | 120000 |
Business Use Cases
- Sales by city
- Employees by department
- Orders by month
- Students by course
- Revenue by product
Best Practices
- Use aliases for aggregates.
- Group only necessary columns.
- Use HAVING for aggregate filtering.
- Verify report calculations.
- Keep reports readable.
Practice Exercises
- Count students by city.
- Calculate salary totals by department.
- Find average salary per department.
- Filter departments having more than 10 employees.
- Create a sales report grouped by city.
Quiz
1. What does GROUP BY do?
2. Which clause filters grouped results?
3. Difference between WHERE and HAVING?
4. Can GROUP BY use multiple columns?
5. Which function counts records?
Summary
- GROUP BY creates categories of records.
- Aggregate functions summarize groups.
- HAVING filters grouped results.
- Multiple columns can be grouped together.
- GROUP BY is essential for reporting.