12. MySQL GROUP BY and HAVING: Complete Guide with Practical Examples

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

🏢 departments Table

💻 laptops Table

🔹 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:

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

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

Output:

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

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

Output:

🔹 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:

🔸 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:

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:

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:

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:

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:

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!