Aggregation and Grouping

Put a CASE inside the aggregate. SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) contributes 1 for each matching row and 0 for the rest, so one GROUP BY query returns a separate count per condition.

COUNT() returns 10 and COUNT(city) returns 7. COUNT() counts rows; COUNT(expression) counts only the rows where that expression is not NULL, so the three NULL cities are skipped.

GROUP BY partitions the rows that survive FROM and WHERE into groups sharing the same grouping-key values, then emits exactly one row per group. The result has as many rows as there are distinct key combinations.

WHERE filters individual rows before grouping; HAVING filters whole groups after aggregation. Only HAVING may reference aggregate results such as COUNT(*) or SUM(amount), because those values exist only once rows have been collapsed into groups.

Group by the column and keep only the groups with more than one row: SELECT email, COUNT() FROM users GROUP BY email HAVING COUNT() > 1. WHERE cannot express this, because the count exists only after grouping.

300, not 100. The join repeats the order row once per matching item, so the same amount is added three times. That row multiplication is join fan-out, and it silently inflates SUM and COUNT.

AVG divides by the number of non-NULL salaries, because aggregates discard NULL inputs first. SUM(salary) / COUNT(*) divides by every row, NULL ones included. The two agree only when the column has no NULLs.

GROUP BY department collapses every department into one output row, and that row has many different name values, so name is ambiguous. Standard SQL requires each select-list column to be a grouping column or sit inside an aggregate.

LISTAGG concatenates a group's values into one delimited string, as in LISTAGG(tag, ', ') WITHIN GROUP (ORDER BY tag). Engines lacking it spell the same idea STRING_AGG or GROUP_CONCAT, and ARRAY_AGG collects the values into an array instead.

AVG's result type is derived from its argument type, and the standard leaves the precision implementation-defined. Engines that keep the integer type for integer input discard the fractional part, so an average of 4 and 5 comes back as 4.

Both return the number of rows where the condition is true. COUNT relies on the missing ELSE producing NULL for non-matching rows, which COUNT ignores. Adding ELSE 0 to the COUNT form breaks it: 0 is not NULL, so it counts every row.

A LEFT JOIN keeps one manufactured row per unmatched customer, with every orders column NULL, and COUNT(*) counts that row. Count a NOT NULL column from the joined side instead — COUNT(o.order_id) — which returns 0.

FILTER restricts the rows one aggregate sees, leaving every other aggregate and the grouping untouched. A WHERE predicate removes rows from the whole query, so it constrains every aggregate at once and can erase entire groups.

GROUP BY treats two NULLs as not distinct, so all rows with a NULL key collapse into one group whose key prints as NULL — even though NULL = NULL evaluates to UNKNOWN in a WHERE predicate.

Use the GROUPING() function: GROUPING(region) returns 1 when that row's grouping set left region out, and 0 when the row is genuinely grouped by region — including when the grouped value itself is NULL.

Aggregate each child separately first — one derived table or CTE per child, grouped by the parent key — then LEFT JOIN those one-row-per-parent summaries back to the parent. Joining both children directly multiplies their rows together.

SUM over an empty input set is defined to return NULL, not 0 — there is nothing to add, and SQL reports "no value" rather than inventing a zero. Wrap it as COALESCE(SUM(amount), 0) when the caller needs a number.

A group's rows have no inherent order, so concatenation order is unspecified unless you order inside the aggregate — WITHIN GROUP (ORDER BY …) or an ORDER BY in the argument list. The query's own ORDER BY sorts result rows, not the elements inside one value.

Group by customer and give each measure its own filter clause: COUNT() for the total, COUNT() FILTER (WHERE status = 'paid') and COUNT(*) FILTER (WHERE status = 'refunded'). One grouped pass produces all three columns.

It adds one subtotal row per region plus one grand-total row on top of the ordinary region-and-product groups. In those extra rows the columns rolled away come back as NULL placeholders.

MIN and MAX return a value of the argument's own type — the earliest and latest DATE, the first and last string. Dates order chronologically; character data orders by the column's collation, so '9' sorts after '10'.

Standard SQL forbids one aggregate taking another as its argument at the same query level: COUNT already collapses each group to a single value, leaving nothing for MAX to aggregate. Compute the counts in a subquery, then aggregate that result.

SUM(DISTINCT amount) removes duplicate amount values within each group and adds what remains, so two rows of 50 contribute 50 once. It is right only when duplicate values are genuinely the same fact, and wrong for money totals.

Group by the row key and write one conditional aggregate per output column: SUM(CASE WHEN status = 'new' THEN 1 ELSE 0 END) AS new_count, one per value. Use MAX(CASE …) when each cell holds a single value rather than a total.

Divide a conditional sum by a total in the same GROUP BY: 100.0 * SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) / COUNT(*). Force numeric arithmetic with the 100.0 literal, and guard any conditional denominator with NULLIF.

No. COUNT(1) counts rows where the constant 1 is not NULL, and a constant is never NULL, so it counts every row — exactly what COUNT(*) does. The choice between them is style, not semantics.

Two. COUNT(DISTINCT expr) counts different non-NULL values, so A and B count once each and the NULL is ignored — it never forms a value of its own.

Move the predicate inside the aggregate as a CASE expression with no ELSE: COUNT(*) FILTER (WHERE c) becomes COUNT(CASE WHEN c THEN 1 END), and SUM(x) FILTER (WHERE c) becomes SUM(CASE WHEN c THEN x END). Non-matching rows become NULL and aggregates skip NULLs.

The customer still appears, with 0. Grouping is decided before any filter clause runs, so the group survives; COUNT over no matching rows is 0, while SUM, AVG, MIN and MAX over no matching rows would be NULL.