GROUP BY

GROUP BY divides rows into groups based on one or more columns, so that aggregate functions (COUNT, SUM, AVG, MIN, MAX) compute separately for each group rather than across all rows. Without GROUP BY, an aggregate function returns one value for the entire result set. With GROUP BY, it returns one value per group — one row per unique combination of GROUP BY column values.

The relationship is fundamental: aggregate functions and GROUP BY are designed to work together. GROUP BY defines the groups; aggregate functions compute statistics for each group. SELECT COUNT(*), SUM(total_amount) FROM orders returns one summary row. SELECT store_id, COUNT(*), SUM(total_amount) FROM orders GROUP BY store_id returns one summary row per store — the aggregates are computed independently within each store's group of rows.

The execution order is critical: GROUP BY runs after WHERE (which filters individual rows before grouping) but before SELECT (which computes the output columns including aggregates). This means: WHERE filters which rows participate in groups; GROUP BY determines the groups; SELECT aggregates within each group. Because GROUP BY runs before SELECT, you cannot use SELECT aliases in GROUP BY in standard SQL — the aliases do not exist yet. And because WHERE runs before GROUP BY, you cannot filter groups using WHERE — that requires HAVING, which runs after GROUP BY.

Every column in the SELECT list of a GROUP BY query must be either (1) listed in the GROUP BY clause, or (2) wrapped in an aggregate function. This is the fundamental rule of grouped queries and it follows directly from what GROUP BY does.

The reason: after GROUP BY, each group is collapsed into one output row. A non-aggregate column like first_name might have different values within a group — if you group by city, one Seattle group might contain customers named Aisha, Rahul, and Priya. The database cannot return a single first_name for this group because there is no single value. It can only return values that are the same for every row in the group (the GROUP BY columns themselves) or computed aggregate values that reduce the group to one number.

PostgreSQL and most standard SQL databases enforce this strictly — a non-aggregate, non-GROUP BY column in SELECT throws an error. MySQL with sql_mode not including ONLY_FULL_GROUP_BY was historically more permissive — it would pick an arbitrary value from the group for non-aggregate columns, which is usually wrong and unpredictable. This is why MySQL queries sometimes appear to "work" without proper GROUP BY usage, but return unreliable results. Always follow the rule: every SELECT column is either in GROUP BY or in an aggregate function.

WHERE and HAVING both filter data, but they operate at different stages of the GROUP BY execution pipeline and on different things. WHERE filters individual rows before groups are formed — it determines which rows participate in grouping. HAVING filters groups after they have been formed and aggregated — it determines which groups appear in the final result.

Concrete example: to find stores with more than 3 delivered orders. WHERE order_status = 'Delivered' filters individual orders to include only delivered ones. HAVING COUNT(*) > 3 filters store groups to include only stores that have more than 3 delivered orders. Both conditions are necessary: WHERE selects which orders count, HAVING selects which stores qualify. Using WHERE COUNT(*) > 3 is an error — WHERE runs before grouping, so aggregate results do not exist yet when WHERE evaluates.

Performance implication: WHERE filtering is almost always more efficient than HAVING filtering for the same condition. WHERE filters rows before they enter the grouping process — fewer rows means less grouping work. HAVING filters after all the grouping and aggregation is done — the work was already performed on the excluded groups. The rule: use WHERE for any condition that can be expressed on individual row values. Use HAVING only for conditions that require aggregate values (COUNT > N, SUM > value, AVG > threshold). Moving a filter from HAVING to WHERE (when the logic allows it) can dramatically improve query performance.

GROUP BY treats all NULL values in a GROUP BY column as belonging to the same group — all rows with NULL in the GROUP BY column are placed together in a single NULL group. This is different from WHERE and JOIN conditions, where NULL ≠ NULL. For grouping purposes, the database considers NULLs to be equal to each other.

This means a GROUP BY query on a nullable column will produce a row in the result for the NULL group, alongside rows for each non-null distinct value. GROUP BY department from employees might produce rows for 'Management', 'Operations', 'Sales', and NULL — the NULL row represents all employees without a department assignment. The aggregate functions compute normally for the NULL group: COUNT(*) counts all NULL-department employees, SUM(salary) adds their salaries.

If you want to exclude the NULL group from results, add WHERE column IS NOT NULL before the GROUP BY — this filters out NULL rows before they can form a group. If you want to give the NULL group a meaningful label in the output, use COALESCE in the GROUP BY expression: GROUP BY COALESCE(department, 'Unassigned') also changes the group value to 'Unassigned' in the SELECT output. Note that grouping by COALESCE(department, 'Unassigned') and department = 'Unassigned' are different: the COALESCE version groups NULLs and actual 'Unassigned' values together, while grouping by the raw column keeps them separate (though in practice, both should not exist in a well-constrained schema).

A basic GROUP BY can find the maximum sales value per category: SELECT category, MAX(units_sold) FROM product_sales GROUP BY category. But this only returns the maximum value — not which product achieved it. To find the specific product name alongside the maximum, you need to join the GROUP BY result back to the original data or use a different approach.

Approach 1 — subquery: SELECT p.category, p.product_name, ps.units_sold FROM product_sales ps JOIN products p ON ... WHERE (p.category, ps.units_sold) IN (SELECT p2.category, MAX(ps2.units_sold) FROM product_sales ps2 JOIN products p2 ON ... GROUP BY p2.category). This finds the product where the (category, units_sold) pair matches the maximum units_sold for that category.

Approach 2 — window functions (the modern approach): SELECT DISTINCT ON (category) category, product_name, units_sold FROM product_sales ORDER BY category, units_sold DESC. In PostgreSQL, DISTINCT ON returns the first row per category after sorting by units_sold descending — effectively the top product per category. The even cleaner approach using ROW_NUMBER(): SELECT category, product_name, units_sold FROM (SELECT category, product_name, units_sold, ROW_NUMBER() OVER (PARTITION BY category ORDER BY units_sold DESC) AS rn FROM product_sales) ranked WHERE rn = 1. This ranks products within each category and selects only rank 1 — the top seller. Window functions (Module 45) are the professional solution for "top N per group" queries.

Cause: A column in SELECT is neither inside an aggregate function nor listed in the GROUP BY clause. When GROUP BY is used, every GROUP BY row represents multiple source rows collapsed into one. A non-aggregate column could have different values across those source rows — the database cannot determine a single value to return, so it refuses the query.

Fix: Add the column to GROUP BY: GROUP BY store_id, first_name. Or wrap it in an aggregate: SELECT MAX(first_name) AS name (picks one value from the group). Or remove it from SELECT if it is not needed. In analytics queries, the most common fix is adding all descriptive columns (name, city, status) to GROUP BY alongside the grouping key.

Cause: The GROUP BY columns have more unique combinations than expected — often because a JOIN introduced extra rows that created new unique combinations, or because a column you thought was a one-to-one with the GROUP BY key actually varies. For example, grouping by store_id and store_name should give one row per store, but if store_name has trailing whitespace in some rows ('Seattle ' vs 'Seattle'), it creates two distinct groups.

Fix: Inspect the GROUP BY columns: SELECT store_id, store_name, COUNT(*) FROM stores GROUP BY store_id, store_name — if this returns more than one row per store_id, there are inconsistent store_name values. Fix the data (TRIM whitespace, normalise capitalisation) or group only by the primary key (store_id) and use MIN(store_name) or MAX(store_name) to get a single name per group in SELECT.

Cause: You tried to use an aggregate function in WHERE. WHERE executes before GROUP BY in the logical order — aggregate values do not exist yet when WHERE runs. WHERE evaluates row-by-row before any grouping occurs, so it cannot reference aggregated values.

Fix: Move the aggregate condition to HAVING, which runs after GROUP BY: GROUP BY store_id HAVING COUNT(*) > 3. HAVING filters groups after they have been formed and aggregated. Use WHERE for conditions on individual row values (column = value, column > threshold), and HAVING for conditions on aggregate results (COUNT(*) > N, SUM(amount) > value, AVG(rating) >= 4.0).

Cause: A many-to-many or one-to-many join multiplied rows before aggregation. If orders are joined to order_items (one order → many items), each order row is duplicated for every item. SUM(orders.total_amount) then adds the total_amount multiple times — once per item in the order. This is called a fan-out bug and is one of the most common data quality issues in analytics queries.

Fix: Aggregate at the correct level before joining. For order-level metrics (total_amount), aggregate in orders first. For item-level metrics (line_total, quantity), aggregate in order_items. Use subqueries or CTEs to pre-aggregate one side of the join: WITH order_totals AS (SELECT order_id, SUM(line_total) AS total FROM order_items GROUP BY order_id) then join order_totals to orders. Or use COUNT(DISTINCT order_id) instead of COUNT(*) to deduplicate after a fan-out join.

Cause: You used a SELECT alias in GROUP BY: SELECT EXTRACT(MONTH FROM order_date) AS month FROM orders GROUP BY month. MySQL and DuckDB allow GROUP BY to reference SELECT aliases. PostgreSQL follows the SQL standard strictly — GROUP BY runs before SELECT, so aliases are not yet defined when GROUP BY executes.

Fix: Repeat the full expression in GROUP BY: GROUP BY EXTRACT(MONTH FROM order_date). For complex expressions that are painful to repeat, use a subquery or CTE: WITH monthly AS (SELECT EXTRACT(MONTH FROM order_date) AS month, total_amount FROM orders) SELECT month, SUM(total_amount) FROM monthly GROUP BY month. The CTE defines the alias first, making it available for GROUP BY in the outer query.

The FreshCart management team needs a store performance summary for their Q1 2024 review (January through March 2024). Write a GROUP BY query that shows for each store: store_id, city, total delivered orders, unique customers served, total delivered revenue (rounded to 2 decimal places), average order value (rounded to 2 decimal places), fastest delivery in days, and a performance_band column using CASE: 'Star' if revenue above ₹3,000, 'Good' if above ₹1,500, 'Needs Support' otherwise. Only include stores that had at least 1 delivered order. Sort by total revenue descending.