Aggregate Functions

What Are Aggregate Functions?

Aggregate functions perform calculations on a set of rows and return a single value. They are essential for summarizing and analyzing data.

💡 Aggregate functions collapse multiple rows into one result. They are commonly used with GROUP BY to summarize data by categories.

Core Aggregate Functions

COUNT

Counts the number of rows.

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

-- Count non-NULL values in a column
SELECT COUNT(phone) FROM employees;

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

-- Count with condition
SELECT COUNT(*) FROM employees WHERE salary > 80000;

SUM

Adds up numeric values.

-- Total salary
SELECT SUM(salary) FROM employees;

-- Total by department
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

-- Sum with condition
SELECT SUM(amount) FROM orders WHERE status = 'completed';

AVG

Calculates the average of numeric values.

-- Average salary
SELECT AVG(salary) AS avg_salary FROM employees;

-- Average by department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Rounded average
SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;

MIN and MAX

Find the minimum and maximum values.

-- Salary range
SELECT
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees;

-- By department
SELECT
    department,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    MAX(hire_date) AS latest_hire
FROM employees
GROUP BY department;

Combining Aggregates

SELECT
    department,
    COUNT(*) AS employee_count,
    SUM(salary) AS total_salary,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
WHERE is_active = TRUE
GROUP BY department
ORDER BY total_salary DESC;

Result:

GROUP BY

Groups rows that have the same values in specified columns.

-- Count employees per department
SELECT department, COUNT(*) AS count
FROM employees
GROUP BY department;

-- Revenue by product category
SELECT
    category,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY category;

Multiple Grouping Columns

-- Revenue by category and region
SELECT
    category,
    region,
    SUM(amount) AS total_revenue
FROM sales
GROUP BY category, region
ORDER BY category, total_revenue DESC;

HAVING

Filters groups after aggregation (unlike WHERE which filters rows before).

-- Departments with more than 10 employees
SELECT department, COUNT(*) AS count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

-- Products with total sales > $10,000
SELECT product_id, SUM(amount) AS total_sales
FROM order_items
GROUP BY product_id
HAVING SUM(amount) > 10000;

WHERE vs HAVING

DISTINCT with Aggregates

-- Count unique customers who placed orders
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

-- List unique departments with employees
SELECT DISTINCT department
FROM employees
WHERE salary > 80000;

✏️ Exercise: Write a query to find: (1) the total revenue per product category, (2) categories with more than 5 products, (3) the average price per category.

See Solution

SELECT
    category,
    SUM(price * quantity) AS total_revenue,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY total_revenue DESC;

✅ Key Takeaways

1. COUNT, SUM, AVG, MIN, MAX are the five core aggregate functions
2. COUNT(*) counts all rows; COUNT(column) ignores NULLs
3. GROUP BY groups rows for per-category aggregation
4. HAVING filters groups after aggregation; WHERE filters rows before
5. You can use multiple aggregate functions in a single SELECT