Learn

Beyond plain GROUP BY, SQL has tools for multi-level aggregates and subtotals.

GROUPING SETS

Multiple groupings in one query — Postgres, SQL Server, BigQuery support it. SQLite does not yet, but UNION ALL is equivalent.

-- Equivalent to GROUP BY country, AND GROUP BY city, AND total
SELECT country, city, SUM(sales)
FROM sales
GROUP BY GROUPING SETS ((country), (city), ());

Gets you per-country totals, per-city totals, AND a grand total — all in one query.

In SQLite, do it explicitly:

SELECT country, NULL AS city, SUM(sales) FROM sales GROUP BY country
UNION ALL
SELECT NULL, city, SUM(sales) FROM sales GROUP BY city
UNION ALL
SELECT NULL, NULL, SUM(sales) FROM sales;

ROLLUP

Hierarchical subtotals — common in financial reports.

SELECT year, quarter, SUM(sales)
FROM sales
GROUP BY ROLLUP (year, quarter);
-- Per (year, quarter), per year, and a grand total.

Gives you: each year/quarter row + each year subtotal + grand total. The hierarchy collapses from finest to coarsest.

CUBE

All possible combinations of grouping columns:

SELECT region, channel, SUM(sales)
FROM sales
GROUP BY CUBE (region, channel);
-- (region, channel) + (region) + (channel) + ()

Useful for OLAP-style reporting where you want every breakdown.

DISTINCT vs GROUP BY

SELECT DISTINCT country FROM users;
-- Same as:
SELECT country FROM users GROUP BY country;

For cardinality, both work. GROUP BY is the right tool when you also want to aggregate. DISTINCT reads more naturally for "unique values of".

HAVING — filter aggregates

WHERE filters rows BEFORE grouping. HAVING filters AFTER:

SELECT country, COUNT(*) AS user_count
FROM users
WHERE active = 1                         -- before grouping
GROUP BY country
HAVING COUNT(*) > 10;                    -- after grouping

Use HAVING only for conditions on aggregates. Conditions on raw columns belong in WHERE (cheaper, can use indexes).

DISTINCT inside aggregates

SELECT COUNT(*) FROM logins;             -- total logins
SELECT COUNT(DISTINCT user_id) FROM logins;  -- unique users
SELECT AVG(DISTINCT salary) FROM emps;   -- average of unique salaries

COUNT(DISTINCT col) is by far the most common — "how many unique X".

FILTER (Postgres, SQLite 3.30+)

Conditional aggregates:

SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'active') AS active_count,
    COUNT(*) FILTER (WHERE status = 'closed') AS closed_count
FROM users;

Much cleaner than:

SELECT
    COUNT(*) AS total,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count
FROM users;

But both work. The CASE form is portable; FILTER is more readable.

Pivot patterns

No SQL standard pivot — but you can fake it with conditional aggregates:

SELECT
    product,
    SUM(CASE WHEN quarter = 'Q1' THEN sales END) AS q1,
    SUM(CASE WHEN quarter = 'Q2' THEN sales END) AS q2,
    SUM(CASE WHEN quarter = 'Q3' THEN sales END) AS q3,
    SUM(CASE WHEN quarter = 'Q4' THEN sales END) AS q4
FROM sales
GROUP BY product;

For real pivot tables: tools like crosstab (Postgres tablefunc extension) or pandas/dplyr in the analytical layer.

Tip: order of clauses

The canonical order in writing SQL:

SELECT ... 
FROM ...
WHERE ...           -- filter rows
GROUP BY ...        -- form groups
HAVING ...          -- filter groups
ORDER BY ...        -- sort
LIMIT ...           -- cap

Logical order of evaluation is different (FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT) — that's why you can't reference SELECT aliases in WHERE. They're not computed yet.