Aggregate Functions

Code

-- Basic aggregation
SELECT dept, COUNT(*) AS count, AVG(salary) AS avg_salary,
    MIN(salary) AS min_sal, MAX(salary) AS max_sal,
    SUM(salary) AS total_sal
FROM employees
GROUP BY dept;

-- HAVING filter
SELECT dept, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept
HAVING AVG(salary) > 50000;

-- Multi-column grouping
SELECT dept, job_title, COUNT(*) AS count
FROM employees
GROUP BY dept, job_title;

-- ROLLUP
SELECT dept, job_title, COUNT(*) AS count
FROM employees
GROUP BY ROLLUP (dept, job_title);

-- Conditional aggregation
SELECT
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count
FROM users;

Explanation

Aggregate functions compute on grouped data; HAVING filters groups; ROLLUP generates subtotals.

More SQL Snippets