SQL REFERENCE

Aggregation

Aggregate functions compute a single result from a set of rows. Combined with GROUP BY, they produce one result per group.

Aggregate Functions

Function	Description	NULL Handling
`COUNT(*)`	Number of rows	Counts all rows including NULLs
`COUNT(col)`	Number of non-NULL values	Ignores NULLs
`SUM(col)`	Sum of values	Ignores NULLs
`AVG(col)`	Arithmetic mean	Ignores NULLs
`MIN(col)`	Minimum value	Ignores NULLs
`MAX(col)`	Maximum value	Ignores NULLs

Basic Aggregation (No GROUP BY)

Without GROUP BY, aggregates operate on all rows and return a single-row result.

SELECT
  COUNT(*) AS total_employees,
  SUM(salary) AS total_payroll,
  AVG(salary) AS avg_salary,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary
FROM employees;

Result:

total_employees	total_payroll	avg_salary	min_salary	max_salary
150	12750000	85000.00	42000	195000

GROUP BY (Single Key)

Groups rows by a column and computes aggregates per group.

SELECT
  department,
  COUNT(*) AS headcount,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Result:

department	headcount	avg_salary
Engineering	45	105000.00
Marketing	30	72000.00
Sales	40	68000.00
Research	35	95000.00

GROUP BY (Multiple Keys)

Group by multiple columns to produce finer-grained aggregates.

SELECT
  department,
  active,
  COUNT(*) AS headcount,
  SUM(salary) AS total_salary
FROM employees
GROUP BY department, active
ORDER BY department, active;

Result:

department	active	headcount	total_salary
Engineering	false	5	475000
Engineering	true	40	4225000
Marketing	false	3	198000
Marketing	true	27	1962000

COUNT Variations

-- Count all rows
SELECT COUNT(*) FROM employees;

-- Count non-NULL values
SELECT COUNT(manager_id) FROM employees;

-- Count distinct values
SELECT COUNT(DISTINCT department) FROM employees;

HAVING

The HAVING clause filters groups after aggregation. It is to GROUP BY what WHERE is to FROM.

-- Departments with average salary above 80k
SELECT
  department,
  AVG(salary) AS avg_salary,
  COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;

Result:

department	avg_salary	headcount
Engineering	105000.00	45
Research	95000.00	35

-- Departments with more than 10 active employees
SELECT department, COUNT(*) AS active_count
FROM employees
WHERE active = TRUE
GROUP BY department
HAVING COUNT(*) > 10
ORDER BY active_count DESC;

WHERE vs. HAVING

Aspect	WHERE	HAVING
Filters	Individual rows	Groups (after aggregation)
Evaluated	Before GROUP BY	After GROUP BY
Can use aggregates?	No	Yes

-- WHERE filters rows before grouping,
-- HAVING filters groups after aggregation
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE active = TRUE              -- filter rows first
GROUP BY department
HAVING AVG(salary) > 80000       -- then filter groups
ORDER BY avg_salary DESC;

Aggregation with Expressions

-- Aggregate computed values
SELECT
  department,
  SUM(salary * 12) AS annual_payroll,
  AVG(salary * (1 + bonus_pct / 100)) AS avg_total_comp
FROM employees
GROUP BY department;

-- Conditional aggregation
SELECT
  department,
  SUM(CASE WHEN active THEN salary ELSE 0 END) AS active_payroll,
  SUM(CASE WHEN NOT active THEN salary ELSE 0 END) AS inactive_payroll
FROM employees
GROUP BY department;