Reference

SQL Aggregate Functions

Summarize many rows into one value, optionally per group.

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

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

Watch out

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

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.