SQL Aggregation
Aggregate Functions Overview
Transform rows into meaningful summaries with aggregate functions.
- COUNT β count rows
- SUM, AVG, MIN, MAX β numeric summaries Aggregate functions turn raw data into insights.
What Are Aggregate Functions?
DfAggregate Functions
Aggregate functions perform a calculation on a set of rows and return a single value. They are used with GROUP BY to summarize data by category.
-- Basic aggregate function usage
SELECT COUNT(*) AS total_employees
FROM employees;
DfGrouped Aggregation
When used with GROUP BY, aggregate functions calculate a value for each group rather than the entire table.
-- Aggregation with GROUP BY
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
Core Aggregate Functions
| Function | Purpose | Returns |
|---|---|---|
| COUNT() | Count rows | Integer |
| SUM() | Total of values | Numeric |
| AVG() | Average value | Numeric |
| MIN() | Smallest value | Same as input |
| MAX() | Largest value | Same as input |
Aggregate functions ignore NULL values by default, except COUNT(*). COUNT(column) excludes NULLs.
COUNT Function
-- Count all rows
SELECT COUNT(*) AS total FROM employees;
-- Count non-null values
SELECT COUNT(phone_number) AS with_phone FROM employees;
-- Count distinct values
SELECT COUNT(DISTINCT department) AS unique_departments FROM employees;
SUM Function
-- Total salary for all employees
SELECT SUM(salary) AS total_payroll FROM employees;
-- Total salary per department
SELECT department, SUM(salary) AS department_total
FROM employees
GROUP BY department;
AVG Function
-- Average salary company-wide
SELECT AVG(salary) AS average_salary FROM employees;
-- Average salary per department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;
MIN and MAX Functions
-- Salary range
SELECT MIN(salary) AS lowest_salary,
MAX(salary) AS highest_salary
FROM employees;
-- Per department
SELECT department,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department;
Combining Multiple Aggregates
-- Single query with multiple aggregate functions
SELECT department,
COUNT(*) AS headcount,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department
ORDER BY headcount DESC;
Avoid using aggregate functions inside WHERE. Use HAVING for aggregate conditions instead.
Aggregate Functions with CASE
-- Conditional aggregation
SELECT department,
COUNT(*) AS total,
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
SUM(CASE WHEN status = 'terminated' THEN 1 ELSE 0 END) AS terminated_count
FROM employees
GROUP BY department;
-- Percentage calculations with aggregates
SELECT department,
COUNT(*) AS total,
ROUND(SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS female_pct
FROM employees
GROUP BY department;
Aggregate Functions with DISTINCT
-- Count unique departments
SELECT COUNT(DISTINCT department) AS unique_depts FROM employees;
-- Average of distinct salaries (rarely useful, but possible)
SELECT AVG(DISTINCT salary) AS avg_distinct_salary FROM employees;
COUNT(DISTINCT col) is valuable for understanding data cardinality. Use it to avoid double-counting.
NULL Handling in Aggregates
-- COUNT(*) counts all rows including NULLs
SELECT COUNT(*) AS total_rows FROM employees;
-- COUNT(column) excludes NULLs
SELECT COUNT(commission_pct) AS non_null_commissions FROM employees;
-- SUM and AVG ignore NULLs automatically
SELECT AVG(bonus) AS avg_bonus FROM employees;
Common Aggregate Patterns
Key Takeaways
- Aggregate functions collapse multiple rows into a single summary value.
- COUNT(*) counts all rows; COUNT(col) excludes NULLs.
- SUM, AVG, MIN, MAX all ignore NULL values automatically.
- Use GROUP BY to aggregate by category; omit it for table-wide aggregation.
- Combine multiple aggregates in one query for efficiency.
| Pattern | Example |
|---|---|
| Total count | SELECT COUNT(*) |
| Unique count | SELECT COUNT(DISTINCT col) |
| Running total | SELECT SUM(col) GROUP BY |
| Category average | SELECT AVG(col) GROUP BY |
| Range detection | SELECT MIN, MAX GROUP BY |