quizzMonkey
Start free
Study notes · 11% of the exam

Aggregation, GROUP BY and HAVING

Aggregates collapse many rows into one value per group. Most interview mistakes come from NULL handling, from the order in which WHERE, GROUP BY and HAVING run, and from expecting an aggregate to tell you which row it came from.

Key points

  1. 1

    COUNT(*) counts rows; COUNT(x) counts non-NULL values; COUNT(DISTINCT x) counts distinct non-NULL values.

  2. 2

    Aggregates skip NULL inputs. With no non-NULL input they return NULL, except COUNT, which returns 0, so wrap totals in COALESCE(SUM(x), 0).

  3. 3

    Without GROUP BY an aggregate query returns exactly one row, even over an empty table; with GROUP BY, an empty input returns zero rows.

  4. 4

    WHERE filters rows before grouping, HAVING filters groups after aggregation, and FILTER (WHERE …) narrows a single aggregate's input.

  5. 5

    Every selected column must be grouped, aggregated, or functionally dependent on a grouped primary key (PostgreSQL ignores UNIQUE for this).

  6. 6

    ROLLUP (a, b) = GROUPING SETS ((a, b), (a), ()); CUBE adds every subset; use GROUPING() to tell subtotal rows from real NULLs.

  7. 7

    Integer division truncates: SUM(x) / COUNT(x) on integers drops the fraction, while AVG(int) returns numeric.

Common traps

  • NULL keys form a single group in GROUP BY, so duplicate checks report NULL as a "duplicate".

  • PostgreSQL rejects output aliases in HAVING and resolves GROUP BY names to input columns first, the opposite of ORDER BY.

  • An average of per-group averages is not the overall average when the groups have different sizes.

Read the source

Test yourself on Aggregation, GROUP BY and HAVING

Ten questions, with the answer and explanation after each one.