✏️ Explanatory Question
Level: Intermediate — The core tools for summarizing and reporting on data.
Aggregate functions perform a calculation on a set of rows and return a single summary value. They are most powerful when combined with the GROUP BY clause to produce per-group summaries.
COUNT(*), which counts all rows including those with NULLs). So AVG(salary) skips rows where salary is NULL.
| Expression | What it counts |
|---|---|
COUNT(*) |
All rows, including NULLs |
COUNT(column) |
Non-NULL values in that column |
COUNT(DISTINCT column) |
Unique non-NULL values |
-- Basic aggregates on the whole table
SELECT
COUNT(*) AS total_employees,
SUM(salary) AS total_payroll,
AVG(salary) AS average_salary,
MIN(salary) AS lowest_salary,
MAX(salary) AS highest_salary
FROM employees;
-- Aggregates per group
SELECT dept_id,
COUNT(*) AS emp_count,
AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id;
-- Count unique departments
SELECT COUNT(DISTINCT dept_id) AS distinct_depts FROM employees;