Aggregate Functions

What Are Aggregate Functions?

Aggregate functions collapse multiple rows into a single value. Instead of returning one row per employee, they return one number that summarises all (or a filtered set of) employees.

-- Without aggregate — returns 8 rows
SELECT salary FROM employees;

-- With aggregate — returns 1 row
SELECT AVG(salary) FROM employees;

The five core aggregate functions:

Function	What it computes
`COUNT()`	Number of rows / non-NULL values
`SUM()`	Total of numeric values
`AVG()`	Arithmetic mean
`MIN()`	Smallest value
`MAX()`	Largest value

Sample Data

CREATE TABLE employees (
    id         INTEGER PRIMARY KEY,
    name       TEXT    NOT NULL,
    department TEXT    NOT NULL,
    salary     REAL    NOT NULL,
    hire_date  TEXT    NOT NULL,
    bonus      REAL    -- nullable
);

INSERT INTO employees VALUES
(1, 'Priya Sharma',  'Finance',    75000, '2022-03-15', 8000),
(2, 'Raj Patel',     'Technology', 92000, '2021-07-01', 12000),
(3, 'Meera Singh',   'Marketing',  68000, '2023-01-10', NULL),
(4, 'Arjun Nair',    'Finance',    81000, '2020-11-22', 9500),
(5, 'Sunita Rao',    'Technology', 88000, '2022-09-05', 11000),
(6, 'Dev Kumar',     'Marketing',  72000, '2021-04-18', 5000),
(7, 'Kiran Mehta',   'Finance',    95000, '2019-06-30', 15000),
(8, 'Ananya Das',    'Technology', 63000, '2024-02-14', NULL);

COUNT

COUNT counts rows. It comes in three forms:

-- COUNT(*) — counts all rows, including those with NULLs
SELECT COUNT(*) AS total_employees FROM employees;
-- Result: 8

-- COUNT(column) — counts non-NULL values in that column
SELECT COUNT(bonus) AS employees_with_bonus FROM employees;
-- Result: 6 (Meera and Ananya have NULL bonus)

-- COUNT(DISTINCT column) — counts unique non-NULL values
SELECT COUNT(DISTINCT department) AS num_departments FROM employees;
-- Result: 3

When to Use Each Form

Form	Use When
`COUNT(*)`	Counting total rows (including NULLs)
`COUNT(col)`	Counting how many rows have a value
`COUNT(DISTINCT col)`	Counting unique values

SUM

SUM adds up all numeric values in a column. It ignores NULL values:

-- Total salary bill
SELECT SUM(salary) AS total_salary_bill FROM employees;
-- Result: 634000

-- Total bonuses paid (NULLs excluded automatically)
SELECT SUM(bonus) AS total_bonuses FROM employees;
-- Result: 60500 (only 6 employees have bonuses)

-- Total compensation (salary + bonus, treating NULL bonus as 0)
SELECT SUM(salary + COALESCE(bonus, 0)) AS total_compensation
FROM employees;

AVG

AVG computes the arithmetic mean. It also ignores NULLs:

-- Average salary
SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;
-- Result: 79250.00

-- Average bonus — ONLY of those who have one
SELECT ROUND(AVG(bonus), 2) AS avg_bonus FROM employees;
-- Result: 10083.33  (6 employees, not 8)

-- Average including non-bonus employees (treat NULL as 0)
SELECT ROUND(AVG(COALESCE(bonus, 0)), 2) AS avg_bonus_all
FROM employees;
-- Result: 7562.50  (60500 / 8)

Key distinction: AVG(bonus) computes the average over 6 rows (ignoring NULLs). AVG(COALESCE(bonus, 0)) computes over all 8 rows. Choose based on your business question.

MIN and MAX

MIN and MAX find the smallest and largest values. They work on numbers, text (alphabetical), and dates:

-- Salary range
SELECT
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary,
    MAX(salary) - MIN(salary) AS salary_range
FROM employees;

Result:

lowest_salary | highest_salary | salary_range
--------------+----------------+-------------
63000         | 95000          | 32000

-- Earliest and latest hire dates
SELECT
    MIN(hire_date) AS oldest_hire,
    MAX(hire_date) AS newest_hire
FROM employees;

-- Alphabetically first and last employee name
SELECT
    MIN(name) AS first_alpha,
    MAX(name) AS last_alpha
FROM employees;

Multiple Aggregates in One Query

You can use multiple aggregate functions in a single SELECT:

SELECT
    COUNT(*)                       AS headcount,
    COUNT(DISTINCT department)     AS departments,
    SUM(salary)                    AS payroll,
    ROUND(AVG(salary), 0)          AS avg_salary,
    MIN(salary)                    AS min_salary,
    MAX(salary)                    AS max_salary,
    MAX(salary) - MIN(salary)      AS range
FROM employees;

Result:

headcount | departments | payroll | avg_salary | min_salary | max_salary | range
----------+-------------+---------+------------+------------+------------+-------
8         | 3           | 634000  | 79250      | 63000      | 95000      | 32000

Aggregates with WHERE

WHERE filters rows before aggregation:

-- Stats for Finance department only
SELECT
    COUNT(*)          AS headcount,
    SUM(salary)       AS payroll,
    ROUND(AVG(salary), 0) AS avg_salary
FROM employees
WHERE department = 'Finance';

headcount | payroll | avg_salary
----------+---------+-----------
3         | 251000  | 83667

-- Stats for employees hired after 2021
SELECT
    COUNT(*)          AS headcount,
    ROUND(AVG(salary), 0) AS avg_salary
FROM employees
WHERE hire_date > '2021-12-31';