Aggregate Functions

COUNT, SUM, AVG, MIN, MAX

Aggregate functions perform calculations across multiple rows and return a single result. They are essential for reporting, analytics, and understanding your data at a high level.

These functions ignore NULL values by default (except COUNT(*), which counts all rows including those with NULLs).

• COUNT(*): Counts the total number of rows
• COUNT(column): Counts non-NULL values in a column
• SUM(column): Adds up all values in a numeric column
• AVG(column): Calculates the average of a numeric column
• MIN(column): Finds the smallest value
• MAX(column): Finds the largest value

-- Count total number of users
SELECT COUNT(*) AS total_users FROM users;

-- Count users who have a bio
SELECT COUNT(bio) AS users_with_bio FROM users;

-- Get the average age
SELECT AVG(age) AS average_age FROM users;

-- Get the youngest and oldest ages
SELECT MIN(age) AS youngest, MAX(age) AS oldest FROM users;

-- Get the sum of all ages (just for demonstration)
SELECT SUM(age) AS total_age FROM users;

-- Combine multiple aggregates in one query
SELECT 
    COUNT(*) AS total,
    AVG(age) AS avg_age,
    MIN(age) AS min_age,
    MAX(age) AS max_age
FROM users;

Aggregates with WHERE

You can combine aggregate functions with WHERE to calculate statistics for a specific subset of your data. The WHERE clause filters the rows first, then the aggregate function operates on the filtered result.

This is extremely useful for generating targeted reports, such as counting active users, calculating average order values for a specific month, or finding the highest-paid employee in a department.

-- Count only active users
SELECT COUNT(*) AS active_users
FROM users
WHERE is_active = TRUE;

-- Average age of users who joined in 2026
SELECT AVG(age) AS avg_age_2026
FROM users
WHERE join_date >= '2026-01-01';

-- Find the oldest active user's age
SELECT MAX(age) AS oldest_active
FROM users
WHERE is_active = TRUE;

-- Count users in a specific age range
SELECT COUNT(*) AS young_users
FROM users
WHERE age BETWEEN 18 AND 25;