Aggregate Functions

SELECT
    COUNT(*)                   AS total_orders,
    COUNT(DISTINCT user_id)    AS unique_customers,
    SUM(total)                 AS revenue,
    AVG(total)                 AS avg_order,
    MIN(total)                 AS smallest_order,
    MAX(total)                 AS largest_order
FROM orders
WHERE status = 'completed';

-- Count all rows (never NULL)
SELECT COUNT(*) FROM users;

-- Count non-NULL in a column
SELECT COUNT(phone) FROM users;       -- excludes rows where phone IS NULL

-- Count distinct values
SELECT COUNT(DISTINCT email) FROM users;

-- Conditional count (CASE trick — portable)
SELECT
    COUNT(CASE WHEN status = 'active'   THEN 1 END) AS active,
    COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive
FROM users;

-- PostgreSQL FILTER clause (cleaner)
SELECT
    COUNT(*) FILTER (WHERE status = 'active')   AS active,
    COUNT(*) FILTER (WHERE status = 'inactive') AS inactive
FROM users;

-- Null-safe sum (NULL rows contribute nothing)
SELECT SUM(revenue) FROM monthly_sales;   -- returns NULL if all rows are NULL

-- Null-safe avg: AVG(col) = SUM(col) / COUNT(col) (not COUNT(*))
SELECT AVG(salary) FROM employees;

-- Weighted average
SELECT SUM(score * weight) / SUM(weight) AS weighted_avg FROM scores;

-- Running total with window function (not a group aggregate)
SELECT id, amount, SUM(amount) OVER (ORDER BY id) AS running_total FROM payments;

-- Conditional sum
SELECT
    SUM(CASE WHEN region = 'NA' THEN revenue ELSE 0 END) AS na_revenue,
    SUM(CASE WHEN region = 'EU' THEN revenue ELSE 0 END) AS eu_revenue
FROM sales;

-- PostgreSQL FILTER
SELECT
    SUM(revenue) FILTER (WHERE region = 'NA') AS na_revenue,
    SUM(revenue) FILTER (WHERE region = 'EU') AS eu_revenue
FROM sales;

-- Works on numeric, date, and string types
SELECT MIN(hire_date), MAX(hire_date) FROM employees;
SELECT MIN(last_name), MAX(last_name) FROM employees;  -- lexicographic

-- First/last row per group (combine with subquery or window function)
-- Most recently created order per user:
SELECT DISTINCT ON (user_id) user_id, id AS order_id, created_at
FROM orders
ORDER BY user_id, created_at DESC;    -- PostgreSQL DISTINCT ON

-- Portable alternative using subquery:
SELECT o.*
FROM orders o
JOIN (
    SELECT user_id, MAX(created_at) AS latest
    FROM orders GROUP BY user_id
) m ON m.user_id = o.user_id AND m.latest = o.created_at;

-- Standard deviation
SELECT STDDEV(salary)       FROM employees;   -- sample std dev
SELECT STDDEV_POP(salary)   FROM employees;   -- population std dev
SELECT STDDEV_SAMP(salary)  FROM employees;   -- same as STDDEV (explicit sample)

-- Variance
SELECT VARIANCE(salary)     FROM employees;   -- sample variance
SELECT VAR_POP(salary)      FROM employees;   -- population variance

-- Percentile (SQL standard / PostgreSQL)
SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS p25,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS p75
FROM employees;

-- Discrete percentile (returns actual value from the set)
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY score) AS median_score
FROM results;

-- Correlation
SELECT CORR(years_experience, salary) FROM employees;

-- Covariance
SELECT COVAR_SAMP(hours, revenue) FROM daily_metrics;

-- PostgreSQL: STRING_AGG
SELECT department, STRING_AGG(name, ', ' ORDER BY name) AS members
FROM employees
GROUP BY department;

-- MySQL: GROUP_CONCAT
SELECT department, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
FROM employees
GROUP BY department;

-- SQL Server: STRING_AGG (2017+)
SELECT department, STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) AS members
FROM employees
GROUP BY department;

-- JSON array of values (PostgreSQL)
SELECT department, JSON_AGG(name ORDER BY name) AS member_list
FROM employees
GROUP BY department;

-- Distinct values only in string agg (PostgreSQL)
SELECT STRING_AGG(DISTINCT country, ', ') FROM users;

-- Array of values (PostgreSQL)
SELECT user_id, ARRAY_AGG(product_id ORDER BY created_at) AS products_bought
FROM order_items
GROUP BY user_id;

-- JSON object aggregate (PostgreSQL)
SELECT JSON_OBJECT_AGG(key_col, value_col) FROM config_table;

-- JSON array (PostgreSQL)
SELECT JSONB_AGG(row_to_json(t)) FROM (SELECT id, name FROM products) t;

-- MySQL: JSON_ARRAYAGG / JSON_OBJECTAGG
SELECT JSON_ARRAYAGG(name) FROM products WHERE category = 'Books';
SELECT JSON_OBJECTAGG(id, name) FROM products;

SELECT
    COUNT(*)                                       AS total,
    COUNT(*) FILTER (WHERE score >= 90)            AS a_students,
    AVG(score) FILTER (WHERE attended = TRUE)      AS avg_if_attended,
    SUM(amount) FILTER (WHERE region = 'US')       AS us_revenue,
    MAX(price) FILTER (WHERE available = TRUE)     AS max_available_price
FROM sales;

-- ROLLUP: hierarchical subtotals
SELECT year, quarter, SUM(revenue)
FROM sales
GROUP BY ROLLUP (year, quarter);
-- Produces: (year,quarter), (year), () — subtotals and grand total

-- CUBE: all combinations of subtotals
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY CUBE (region, product);
-- Produces every combination: (region,product), (region), (product), ()

-- GROUPING SETS: explicit combinations
SELECT region, product, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS (
    (region, product),
    (region),
    ()
);

-- GROUPING() function: 1 when that column is the rollup NULL, 0 otherwise
SELECT
    GROUPING(year)    AS is_year_total,
    GROUPING(quarter) AS is_qtr_total,
    year, quarter, SUM(revenue)
FROM sales
GROUP BY ROLLUP(year, quarter);

-- Aggregate: one row per department
SELECT department, AVG(salary) AS dept_avg
FROM employees
GROUP BY department;

-- Window function: keeps all rows, adds the average alongside each row
SELECT
    name, salary, department,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;