Cheatsheet

SQL Aggregates & Grouping Cheatsheet

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

The functions

Function	Returns
`count(*)`	Number of rows (counts nulls).
`count(col)`	Rows where `col` is not null.
`count(distinct col)`	Distinct non-null values.
`sum(col)` / `avg(col)`	Total / average of non-null values.
`min(col)` / `max(col)`	Smallest / largest non-null value.

One row per group

Products and average price per category

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

Every non-aggregated column in SELECT must appear in GROUP BY.

WHERE vs HAVING

Clause	Filters	Can use an aggregate?
`WHERE`	Rows, before grouping	No
`HAVING`	Groups, after grouping	Yes

Both together

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

Turning counts into a rate

Percent shipped

select round(
         100.0 * count(*) filter (where status = 'shipped') / count(*),
         1
       ) as pct_shipped
from   orders

Watch out

The common aggregates shown above ignore null input values, while count(*) counts rows regardless of nulls. Some other aggregates, including DuckDB's list, first, and last, can retain nulls, so check a function's behavior when that distinction matters. Start a percentage with 100.0 (a decimal) so the pattern stays portable to databases where integer division truncates.

Lesson

Counting Rows with SQL COUNT

Turn rows into counts.

Reference

SQL Aggregate Functions

COUNT, SUM, AVG, MIN, MAX, 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.