SQL REFERENCE

Aggregation

Group rows and compute aggregate values with GROUP BY, HAVING, and aggregate functions.

Aggregate Functions

Function	Description
SUM(expr)	Sum of values
AVG(expr)	Average of values
MIN(expr)	Minimum value
MAX(expr)	Maximum value
COUNT(*)	Count all rows
COUNT(expr)	Count non-null values
COUNT(DISTINCT expr)	Count distinct values
STDDEV(expr)	Sample standard deviation
STDDEV_POP(expr)	Population standard deviation
VARIANCE(expr)	Sample variance
VAR_POP(expr)	Population variance

Global Aggregation (No GROUP BY)

SELECT SUM(val) FROM agg;           -- 210.0
SELECT AVG(val) FROM agg;           -- 35.0
SELECT MIN(x) FROM agg;             -- 100
SELECT MAX(x) FROM agg;             -- 600
SELECT COUNT(*) FROM agg;           -- 6

GROUP BY

-- Single key
SELECT grp, SUM(val) FROM agg GROUP BY grp;
-- grp=1: 60.0, grp=2: 150.0

-- Multiple keys
SELECT cat, grp, SUM(val) FROM agg GROUP BY cat, grp;

Multiple Aggregates

SELECT grp, SUM(val), AVG(val), COUNT(*)
FROM agg GROUP BY grp;
-- grp=1: 60.0, 20.0, 3
-- grp=2: 150.0, 50.0, 3

HAVING

SELECT grp, SUM(val) FROM agg
GROUP BY grp HAVING SUM(val) > 100.0;
-- Only grp=2: 150.0

Aggregate Expressions

SELECT SUM(val * 2.0) FROM agg;     -- 420.0
SELECT AVG(val + 10.0) FROM agg;    -- 45.0

ORDER BY Aggregate

SELECT grp, SUM(val) AS total
FROM agg GROUP BY grp ORDER BY total DESC;
-- grp=2: 150.0, grp=1: 60.0

FILTER Clause

SELECT SUM(v1) FILTER (WHERE id1 = 'id001') FROM csv;        -- 10.0
SELECT COUNT(v1) FILTER (WHERE id1 = 'id001') FROM csv;      -- 4.0
SELECT id2, SUM(v1) FILTER (WHERE id4 = 1)
FROM csv GROUP BY id2 ORDER BY id2;

-- Multiple aggregates with different filters
SELECT SUM(v1) * 1.0, SUM(v1) FILTER (WHERE id1 = 'id001') FROM csv;
-- 110.0, 10.0

Statistical Functions

SELECT id1, STDDEV(v1) FROM csv GROUP BY id1 ORDER BY id1 LIMIT 1;
-- id001, 1.290994

SELECT STDDEV_POP(v1) FROM csv GROUP BY id1 ORDER BY id1 LIMIT 1;
-- 1.118034

SELECT VARIANCE(v1) FROM csv GROUP BY id1 ORDER BY id1 LIMIT 1;
-- 1.666667

SELECT VAR_POP(v1) FROM csv GROUP BY id1 ORDER BY id1 LIMIT 1;
-- 1.25