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