GROUP BY & HAVING โ Analyzing Data by Categories ๐
In Part 5, you learned how aggregate functions answer questions like:
What is the total revenue?
How many customers do we have?
But real-world business questions are usually more specific:
What is the revenue by city?
How many employees are there in each department?
Which products generated the most revenue?
That's where GROUP BY comes in.
1๏ธโฃ What is GROUP BY?
GROUP BY combines rows with the same value into groups so that aggregate functions can calculate a metric for each group.
Basic Syntax
SELECT
column_name,
aggregate_function(column)
FROM table_name
GROUP BY column_name;
Example:
SELECT
department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department;
Instead of getting one total employee count, you get a count for each department.
2๏ธโฃ Why Do We Need GROUP BY?
Without GROUP BY:
SELECT COUNT(*) AS total_employees
FROM employees;
Result: 1000
This answers: How many employees are there?
But:
SELECT
department,
COUNT(*) AS employee_count
FROM employees
GROUP BY department;
Result:
โข IT | 350
โข Finance | 200
โข HR | 120
โข Sales | 330
Now you can answer: How many employees are in each department?
3๏ธโฃ GROUP BY With COUNT()
This is probably the most common GROUP BY pattern.
SELECT
city,
COUNT(*) AS customer_count
FROM customers
GROUP BY city;
4๏ธโฃ GROUP BY With SUM()
Suppose you want revenue by city.
SELECT
city,
SUM(amount) AS total_revenue
FROM orders
GROUP BY city;
This is a common business KPI.
5๏ธโฃ GROUP BY With AVG()
Calculate average salary by department:
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;
6๏ธโฃ GROUP BY With MIN() and MAX()
You can use multiple aggregate functions.
SELECT
department,
MIN(salary) AS minimum_salary,
MAX(salary) AS maximum_salary,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;
7๏ธโฃ Multiple Aggregations
You aren't limited to one metric.
SELECT
department,
COUNT(*) AS employees,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary,
MIN(salary) AS minimum_salary,
MAX(salary) AS maximum_salary
FROM employees
GROUP BY department;
This is the foundation of many analytical reports.
8๏ธโฃ GROUP BY Multiple Columns
You can group by more than one column.
Example: Count customers by city and customer segment.
SELECT
city,
customer_segment,
COUNT(*) AS customer_count
FROM customers
GROUP BY
city,
customer_segment;
SQL creates a group for each unique combination.
9๏ธโฃ Understanding Multiple GROUP BY Columns
Grouping by
GROUP BY city, segment creates groups like:โข Mumbai + Premium
โข Mumbai + Standard
โข Delhi + Premium
The combination matters.
๐ GROUP BY With WHERE
WHERE filters rows before grouping.
Example: Calculate revenue by city for completed orders only.
SELECT
city,
SUM(amount) AS revenue
FROM orders
WHERE order_status = 'Completed'
GROUP BY city;
Conceptually: All Orders โ WHERE Completed โ GROUP BY City โ SUM Revenue
1๏ธโฃ1๏ธโฃ WHERE vs GROUP BY
WHERE Answers: Which rows should be included?
GROUP BY Answers: How should those rows be divided into groups?
1๏ธโฃ2๏ธโฃ What is HAVING?
HAVING filters groups after aggregation.
Example: Find departments with more than 100 employees.