HowToCodePage

Aggregation and GROUP BY

COUNT, SUM, AVG and friends; HAVING versus WHERE; and why COUNT(*) differs from COUNT(col).

Aggregate functions

FunctionResult
COUNT(*)Number of rows — includes NULLs
COUNT(col)Non-NULL values only
COUNT(DISTINCT col)Unique non-NULL values
SUM(col)/AVG(col)NULLs ignored in the maths
MIN/MAXExtremes; work on dates and text too
STRING_AGG(col, ', ')Concatenate grouped values
💡
AVG(col) averages over non-NULL rows only, so it silently ignores missing data rather than treating it as zero. Decide which behaviour you actually want.

Grouping

SELECT category, COUNT(*) AS n, AVG(price) AS avg_price
FROM products
WHERE active = TRUE
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY n DESC;
  • Every non-aggregated column in SELECT must appear in GROUP BY.
  • WHERE filters rows before grouping; HAVING filters the resulting groups.
  • Grouping by an expression? Repeat it in both clauses, or use a CTE with an alias.

Window functions (a glimpse)

When you need aggregates but want to keep every underlying row, window functions are the answer — no grouping collapse required.

SELECT name, category, price,
       AVG(price) OVER (PARTITION BY category) AS cat_avg,
       RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

FAQ

Why COUNT(col) lower than COUNT(*)?
Aggregates ignore NULL. Use COUNT(*) to count rows and COUNT(DISTINCT col) to count unique values.
Can I use an alias in HAVING?
In most databases yes for HAVING but no for WHERE. PostgreSQL allows referencing select aliases in GROUP BY/ORDER BY only.

SELECT: reading data Indexes and query speed

Last refreshed 2026-09-17.