SQL grouping concepts

1. GROUP BY

GROUP BY is used to group rows that have the same values in one or more columns. It’s often used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), MIN().

Example:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

👉 This query returns the number of employees in each department.

2. HAVING

HAVING is like a WHERE clause, but it’s applied after grouping. You use it to filter groups.

Example:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

👉 This returns only departments that have more than 5 employees.
(WHERE cannot be used here because COUNT() is an aggregate function.)

3. ROLLUP

ROLLUP is used with GROUP BY to generate subtotals and grand totals automatically.

Example:

SELECT department, job_title, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP (department, job_title);

SELECT department, job_title, SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP (department, job_title);

👉 This query will produce:

• Total salary for each (department, job_title) pair
• Total salary for each department (subtotal)
• Total salary for all employees (grand total)

🔹 Example Data (Employees Table)

🔹 Sample ROLLUP Output

For the query above:

✅ Summary:

• GROUP BY → groups rows.
• HAVING → filters groups (after aggregation).
• ROLLUP → adds subtotals & grand totals.