The snippet library.
Small, practical examples. Search by task or narrow things down below.
Calculate each group’s integer percentage of the total
Postgres: Multiply before division and protect against a zero denominator.
Postgres✓ Tested
Calculate each group’s numeric spread
Postgres: Subtract MIN from MAX to report the difference between extremes.
Postgres✓ Tested
Compute an integer weighted average
Postgres: Multiply before summing and guard a zero total weight with NULLIF.
Postgres✓ Tested
Count distinct values within a result
Postgres: COUNT DISTINCT counts distinct non-null values.
Postgres✓ Tested
Count rows satisfying separate conditions
Postgres: SUM over CASE expressions calculates several conditional counts in one scan.
Postgres✓ Tested
Distinguish row count from non-null value count
Postgres: COUNT(*) includes every row while COUNT(column) skips NULL values.
Postgres✓ Tested
Filter rows before grouping
Postgres: WHERE limits the rows contributing to the aggregate; it differs from filtering completed groups with HAVING.
Postgres✓ Tested
Group by more than one field
Postgres: Each distinct combination of grouping fields produces its own row.
Postgres✓ Tested
Return zero for a sum over no rows
Postgres: SUM produces NULL for an empty input; COALESCE supplies the desired reporting default.
Postgres✓ Tested
Turn known group values into columns
Postgres: Conditional aggregation gives a portable pivot when the category set is known.
Postgres✓ Tested