Lesson 14 Intermediate Level
14 of 30 lessons

GROUP BY and HAVING

Learn how to group data and filter groups using GROUP BY and HAVING clauses. These are essential for data analysis and reporting.

What is GROUP BY?

The GROUP BY clause groups rows that have the same values into summary rows, like "total salary by department". It's commonly used with aggregate functions like SUM(), AVG(), COUNT(), etc.

It's commonly used with aggregate functions like SUM(), AVG(), COUNT(), etc.

What is HAVING?

The HAVING clause filters the results of grouped data, similar to how WHERE filters rows before grouping. HAVING is applied after the GROUP BY is performed.

Syntax

SELECT column, AGG_FUNCTION(column)
FROM table
GROUP BY column
HAVING condition;

Examples

-- Total salary by department
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;

-- Departments with total salary over 50,000
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 50000;

-- Jobs with more than 3 employees
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id
HAVING COUNT(*) > 3;

Practice Task

  • Group employees by department and display total number of employees.
  • Use HAVING to find departments with more than 5 employees.
  • Show average salary by job role, and use HAVING to display only those with AVG > 6000.
Previous Lesson Next Lesson