SQL — Group By

Basic GROUP BY

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

Multiple columns

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

GROUP BY with HAVING

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

Aggregate functions

SELECT
  department,
  COUNT(*) AS total,
  AVG(salary) AS avg_salary,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary,
  SUM(salary) AS total_salary
FROM employees
GROUP BY department;

GROUP BY with ROLLUP

SELECT department, status, COUNT(*)
FROM employees
GROUP BY ROLLUP(department, status);

GROUP BY with CUBE

SELECT department, status, COUNT(*)
FROM employees
GROUP BY CUBE(department, status);

Mini Practice

Write SQL code that:

1. Groups by a single column
2. Uses HAVING to filter groups
3. Uses multiple aggregate functions
4. Demonstrates ROLLUP

Up Next

In the next lesson, you'll learn about Subqueries — queries within queries.