Aggregation and Grouping

Yes — GROUP BY accepts any value expression over the input columns, and groups are keyed on the computed value. Bucketing a timestamp by day means grouping on a truncated form, such as CAST(created_at AS DATE), not on the raw timestamp.

With no GROUP BY, an aggregate query treats the whole filtered input as one implicit group and always returns exactly one row, even if nothing matched. Add GROUP BY and empty input yields zero groups, so zero rows.

Grouping still happens: rows are partitioned by the key and each group emits its key, so the result is the distinct key combinations — the same row set SELECT DISTINCT would give over those columns. GROUP BY additionally supports aggregates and group-level filtering.

ROLLUP generates the n+1 prefixes of the column list, so four grouping sets for three columns. CUBE generates every subset — 2^n, or eight for three columns — including combinations that skip a leading column.

Write GROUP BY GROUPING SETS ((region), (product), ()) with SUM in the select list. Each listed set becomes its own aggregation level in one result, and the empty set () yields the grand total.

Without GROUP BY, every row surviving WHERE forms one implicit group, and HAVING is a single test over that group's aggregates. The query returns either the one aggregate row or no rows at all.

WHERE drops low-paid employees before grouping, so a department appears if it has any high earner and its reported average covers only those employees. HAVING keeps everyone in the average and returns only departments whose overall average exceeds 50000.

COUNT(DISTINCT pk) works because a primary key identifies a row uniquely, so collapsing repeats restores the true row count. SUM(DISTINCT amount) deduplicates values, not rows: two different orders of 100 collapse into a single 100.

Compare the joined row count with the parent's: if COUNT() exceeds COUNT(DISTINCT parent_pk), the join duplicated parent rows and every additive aggregate over parent columns is inflated. Probe the child's key with GROUP BY … HAVING COUNT() > 1 first.

Exactly one row, holding 0 and NULL. An aggregate query with no GROUP BY always produces one row; COUNT of an empty input is 0, while SUM of an empty input is NULL. Adding GROUP BY would return zero rows instead.

A NULL bonus makes base + bonus NULL for that row, so the aggregate skips the row entirely and loses its base too. SUM(base) + SUM(bonus) drops only the missing bonus. Fix with SUM(base + COALESCE(bonus, 0)).

Grouping by a primary key gives each group at most one row of that table, so every other column of it has exactly one value per group. SQL:1999 permits selecting such functionally dependent columns without listing them.

No. String aggregation ignores NULL inputs, so LISTAGG, STRING_AGG and GROUP_CONCAT produce no element for them; ARRAY_AGG keeps NULLs as array elements. Over a group with no rows at all, both return NULL rather than an empty string or empty array.

SUM derives its result type from its argument. Engines that keep INTEGER for INTEGER input can exceed that range on a large table and raise an arithmetic overflow. Prevent it by casting the argument to a wider type inside the aggregate.

WHERE removes rows before grouping, so a customer whose orders were all filtered out has no rows left, forms no group, and produces no output row. A condition inside CASE keeps every row in the group and reports a 0 instead.

Count the rows of a de-duplicating derived table: SELECT COUNT(*) FROM (SELECT DISTINCT customer_id, product_id FROM sales) d. A multi-argument COUNT(DISTINCT a, b) is not portable — several engines accept only one expression.

GROUP BY forms groups only from key values present in its input, so a day with no rows produces no group and no output row. To show every day you must supply the key values from elsewhere — typically a calendar table left-joined to the aggregated result.

GROUP BY creates a group only where rows exist, so a customer with no orders produces no group for HAVING to test. HAVING can only remove groups, never invent them, and every surviving group has COUNT(*) of at least 1.

Fan-out turns the average into one weighted by each parent's child count: orders with many items count many times. Both numerator and denominator are inflated by row-specific factors, so no DISTINCT wrapper repairs it — only removing the multiplication does.

They answer different questions. AVG(COALESCE(rating, 0)) counts unrated rows as zero and lowers the average; COALESCE(AVG(rating), 0) leaves the average of rated rows untouched and only substitutes a value when there is no data at all.

Each aggregate is evaluated independently over the whole group, so MIN(order_date) and MIN(order_total) can come from different rows. The output row is a composite that may match no real row, and no error is raised.

The engine caps the aggregated result's length. MySQL truncates GROUP_CONCAT at group_concat_max_len — 1024 bytes by default — emitting only a warning. Raise the limit for known-bounded lists, or return rows or an array instead of one unbounded string.

Standard SQL says no: GROUP BY is evaluated before the select list is projected, so output aliases do not exist yet and you must repeat the expression. Several engines allow aliases or ordinals as an extension.

Where the engine allows it, put DISTINCT inside the aggregate: string_agg(DISTINCT tag, ', ' ORDER BY tag). With DISTINCT the sort key must be the aggregated expression itself, so ordering by another column is rejected. The portable fallback aggregates over a SELECT DISTINCT derived table.

The filter clause belongs to aggregate-function syntax: it goes after the closing parenthesis of the argument list, so COUNT(DISTINCT x) FILTER (WHERE c) is legal and DISTINCT stays inside. With OVER it sits before it. ROW_NUMBER() is not an aggregate and takes no filter.

Each grouping set is computed independently from the base rows, so the grand-total AVG is the sum of all values over the count of all rows. Averaging the subtotal averages instead weights every group equally.