✦

AI Reading

Quick summary of this article

This guide explains how to use the GROUP BY and HAVING clauses in MySQL to organize and filter data when working with aggregates. It uses sample employee, department, and laptop tables to demonstrate practical examples, starting with single-table queries and then combining them with JOINs for more complex insights.

• GROUP BY groups rows that have the same values into summary rows, such as counting employees per department or laptops per brand.
• HAVING filters groups after aggregation, allowing you to show only groups that meet a condition, like managers with more than one direct report.
• Both clauses can be used with JOINs to combine data from multiple tables, such as showing department names with employee counts or laptop brand counts per department.
• You can use COUNT(DISTINCT column) with HAVING to find groups with a certain number of unique values, like departments with more than one laptop brand.
• HAVING can also work with subqueries to find top results, such as identifying the manager(s) with the most employees.

In relational databases, the GROUP BY and HAVING clauses in MySQL are powerful tools used to organize and filter data when working with aggregates. In this comprehensive tutorial, we will explore how these clauses work—first using single tables, and then how they can be combined with joins for deeper insights.

We’ll use the following sample tables for all our examples:

👨‍💼 employees Table

employee_id	name	department_id	manager_id
1	Alice	1	NULL
2	Bob	2	1
3	Charlie	3	1
4	David	NULL	2
5	Eva	2	2

🏢 departments Table

department_id	department_name
1	HR
2	IT
4	Marketing

💻 laptops Table

laptop_id	employee_id	brand
1	1	Dell
2	2	HP
3	3	Lenovo
4	5	Apple

🔹 GROUP BY with Single Table

Example 1: Count of Employees per Department (from employees table only)

SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id;

Output:

department_id	total_employees
1	1
2	2
3	1
NULL	1

Example 2: Number of Reportees per Manager (from employees table)

SELECT manager_id, COUNT(*) AS total_reportees
FROM employees
GROUP BY manager_id;

Output:

manager_id	total_reportees
1	2
2	2
NULL	1

Example 3: Count of Each Laptop Brand (from laptops table only)

SELECT brand, COUNT(*) AS brand_count
FROM laptops
GROUP BY brand;

Output:

brand	brand_count
Dell	1
HP	1
Lenovo	1
Apple	1

🔹 HAVING with Single Table

Example 4: Managers with More Than 1 Reportee (from employees table)

SELECT manager_id, COUNT(*) AS total_reportees
FROM employees
GROUP BY manager_id
HAVING total_reportees > 1;

Output:

manager_id	total_reportees
1	2
2	2

🔸 GROUP BY and HAVING with JOINs

Example 5: Department Name with Employee Count

SELECT d.department_name, COUNT(e.employee_id) AS total_employees
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

Output:

department_name	total_employees
HR	1
IT	2
NULL	1
Marketing	0

Example 6: Departments with More Than 1 Employee

SELECT d.department_name, COUNT(e.employee_id) AS total_employees
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
HAVING total_employees > 1;

Output:

department_name	total_employees
IT	2

Example 7: Count of Laptop Brands per Department

SELECT d.department_name, l.brand, COUNT(*) AS count
FROM laptops l
JOIN employees e ON l.employee_id = e.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name, l.brand;

Output:

department_name	brand	count
HR	Dell	1
IT	Apple	1
IT	HP	1

Example 8: Departments With More Than One Laptop Brand

SELECT d.department_name, COUNT(DISTINCT l.brand) AS unique_brands
FROM laptops l
JOIN employees e ON l.employee_id = e.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
HAVING unique_brands > 1;

Output:

department_name	unique_brands
IT	2

Example 9: Top Manager with Most Employees

SELECT e.manager_id, COUNT(*) AS total_reportees
FROM employees e
GROUP BY e.manager_id
HAVING total_reportees = (
  SELECT MAX(manager_count)
  FROM (
    SELECT manager_id, COUNT(*) AS manager_count
    FROM employees
    GROUP BY manager_id
  ) AS subquery
);

Output:

manager_id	total_reportees
1	2
2	2

Summary

• ✅ GROUP BY groups rows for aggregation.
• ✅ HAVING filters groups after aggregation.
• ✅ Both can be used on single tables or with JOINs.
• ✅ Useful for reporting on employee distribution, laptop allocation, and managerial performance.

Practice Exercises

1. Find departments with fewer than 2 employees.
2. List managers who have more than one direct report.
3. Count how many employees have a laptop of each brand.
4. Identify which department has the most laptop brands.

Use these examples to practice and master GROUP BY and HAVING in MySQL!