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 value.

One row per group

Revenue 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 inGROUP 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

Aggregates ignore NULL (except count(*)). And 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.

Lesson

SQL SUM and AVG Aggregates

Calculate totals and averages.

Lesson

SQL GROUP BY: Grouping Rows

Summarize rows by category.

Lesson

SQL HAVING: Filtering Groups

Filter after GROUP BY.