Reference

SQL Aggregate Functions

Summarize many rows into one value, optionally per group.

Aggregate functions collapse a set of rows into a single value. Without GROUP BY they summarize the whole result; with it, they produce one row per group.

Practice

Practice GROUP BY Practice HAVING Practice COUNT Practice SUM and AVG Practice bool_and, bool_or, and FILTER

The core functions

Function	Returns
`COUNT(*)`	Number of rows (including nulls).
`COUNT(col)`	Number of rows where `col` is not null.
`COUNT(DISTINCT col)`	Number of distinct non-null values.
`SUM(col)`	Total of the values.
`AVG(col)`	Average of non-null values.
`MIN(col)` / `MAX(col)`	Smallest / largest value.

Grouping

One row per group

select   category_id,
         count(*)   as product_count,
         avg(price) as avg_price
from     products
group by category_id
order by avg_price desc

Every column in SELECT that is not inside an aggregate must appear in GROUP BY. Otherwise the database can't tell which value to show for the group.

WHERE vs HAVING

WHERE filters rows before grouping; HAVING filters groups after. You cannot use an aggregate in WHERE.

Filter groups

select   category_id, count(*) as product_count
from     products
where    discontinued = false   -- per-row filter
group by category_id
having   count(*) >= 3          -- per-group filter

Yes/no per group, and conditional counts

bool_and(condition) is true when every row in the group meets the condition, and bool_or(condition) when at least one does. FILTER (WHERE ...) after any aggregate feeds it only the matching rows:

Any, every, and how many

select   category_id,
         bool_or(quantity_on_hand <= reorder_level) as any_low_stock,
         bool_and(not discontinued)                 as all_on_sale,
         count(*) filter (where price >= 20)        as priced_20_plus
from     products
group by category_id
order by category_id

Engine	Every / any	Count matches
DuckDB	`bool_and` / `bool_or`	`FILTER`, or `count_if`
PostgreSQL	`bool_and` (or `every`) / `bool_or`	`FILTER`
SQLite	`min(flag)` / `max(flag)`	`FILTER`, or `sum(flag)`
MySQL	`min(flag)` / `max(flag)`	`sum(flag)`
SQL Server	`min` / `max` of a CASE that gives 1 or 0	`sum` of that CASE

Both bool_ functions skip NULL, so bool_and means "every known value". In DuckDB, count_if returns NULL rather than 0 when every condition is NULL; count(*) filter (where ...) returns 0.

Watch out

Aggregates ignore NULL (except COUNT(*)). So AVG(price) divides by the count of non-null prices, not the total row count. That is usually what you want.

Lesson

Counting Rows with SQL COUNT

Turn rows into counts.

Cheatsheet

SQL Aggregates Cheatsheet

COUNT, SUM, AVG, GROUP BY, and HAVING.

Interview

SQL GROUP BY Interview Questions

Practice aggregate reports, HAVING, and answer shape.

Mistake

Why My Percentage Comes Out 0

Avoid truncating fractional results.