GROUP BY

Hook: GROUP BY is the SQL way to turn a messy pile of rows into tidy stacks — interviewers love it because it shows whether you understand how SQL thinks about groups, not just records.

Question: What does GROUP BY do in SQL?

Answer: GROUP BY collects rows that share the same values in one or more columns, then lets aggregate functions like COUNT, SUM, AVG, MIN, and MAX run once per group. It is how you ask questions like 'sales per day' or 'orders per customer' instead of looking at every row separately.

Interview-Ready Answer: I use GROUP BY when I need one result row per category instead of one row per record. It first groups rows by the chosen columns, then aggregates each group, so a query like 'total sales by region' becomes one row for each region. A key detail is that WHERE filters rows before grouping, while HAVING filters groups after aggregation.

What GROUP BY really means

GROUP BY changes the shape of your result. Instead of returning every row, SQL builds buckets of rows that share the same key values, then calculates one output row per bucket. If you group 10,000 sales rows by region, you might get only 5 final rows back.

1. SQL reads the source rows from FROM and joins them if needed.
2. WHERE removes rows that should never be considered. This happens before groups exist.
3. GROUP BY forms groups using the grouping key, which can be one column, multiple columns, or an expression like COALESCE(region, 'Unknown').
4. Aggregate functions run once per group. For example, SUM(amount) adds values inside each bucket, and COUNT(*) counts rows in that bucket.
5. HAVING filters whole groups after aggregation. This is where you keep only groups whose total is big enough.
6. SELECT returns the grouped columns and aggregate results, and ORDER BY can sort the final summary.

Important mental model: grouping is not sorting. The engine may use hashing or sorting internally, but the SQL result is not guaranteed to come out in grouped order unless you add ORDER BY.

When to use it

• To summarize data: revenue by day, orders by customer, clicks by campaign.
• To count distinct categories in a business-friendly way.
• To find groups that pass a threshold, like 'customers with more than 5 orders'.
• To combine row-level data into reports, dashboards, and analytics queries.

GROUP BY vs related clauses

DISTINCT can look similar to GROUP BY when you only select the grouping columns, but it cannot compute summaries. If you need totals, averages, or counts, you need GROUP BY.

Performance and engine behavior

Most SQL engines use one of two main strategies. Hash aggregation builds an in-memory hash table keyed by the grouping columns; on average it is close to O(n) time, with memory around O(g) where g is the number of groups. Sort aggregation sorts rows by the grouping key first; that is typically O(n log n), but it can be efficient if the data is already ordered or if an index helps.

Realistically, grouping 1,000,000 rows into 12 months is cheap. Grouping 1,000,000 rows into 900,000 unique user IDs is much heavier because the engine has almost as many groups as rows. If memory is too small, the engine may spill to disk, which slows the query a lot.

Edge cases interviewers like

• NULLs: rows with NULL in the same grouping column belong to one group together.
• COUNT(*) vs COUNT(column): COUNT(*) counts rows, while COUNT(column) ignores NULL values.
• Non-grouped columns: standard SQL does not let you select a column that is neither grouped nor aggregated.
• Empty input: a grouped query returns no group rows if nothing survives filtering.
• Expressions: grouping by DATE(order_ts) or COALESCE(...) groups by the computed value, not the raw column.

Real-World Example: Imagine a checkout service in an e-commerce app that powers a finance dashboard. Every hour, it runs a report for revenue by region and payment status using GROUP BY, and the business team checks whether sales are trending up or down.

One week, an engineer moved a filter from HAVING to WHERE because it looked cleaner. The query now removed rows before the sums were built, so regions with lots of small orders disappeared from the dashboard. The symptom was a sudden drop in reported revenue, logs showing fewer input rows than expected, and finance asking why the daily total no longer matched the payment gateway.

What went wrong: the team filtered the raw data instead of the grouped totals. The fix was to keep row-level filters in WHERE, move total-based rules to HAVING, and add an ORDER BY so the report was stable and easy to read.

Follow-up & Tricky Questions:

• How is GROUP BY different from DISTINCT? DISTINCT removes duplicate rows, while GROUP BY creates buckets so aggregates can be calculated. If you only need unique values, DISTINCT is simpler; if you need totals or counts, use GROUP BY.
• What is HAVING used for? HAVING filters after grouping, so it is the right place for conditions like SUM(amount) > 1000 or COUNT(*) > 5. It is the post-aggregation cousin of WHERE.
• Can I select a column that is not in GROUP BY? In standard SQL, no, unless it is wrapped in an aggregate. Many modern databases enforce this strictly; older MySQL modes could return an arbitrary value, which is dangerous and non-portable.
• What happens with NULL values? NULLs in the grouping key are grouped together as one bucket. Inside aggregates, COUNT(column) ignores NULL, while COUNT(*) still counts the row.
• Does GROUP BY sort the output? No. It groups rows, but output order is undefined unless you add ORDER BY. Some engines may appear to return sorted results, but you should never rely on that.
• What if I need row detail and a total? Use a window function such as SUM(amount) OVER (PARTITION BY region). That keeps each original row and adds a per-group summary beside it.
• Can WHERE be used with aggregates? No, because WHERE runs before aggregation. A condition based on a group total belongs in HAVING.
• Does GROUP BY work on expressions? Yes. You can group by computed values like dates, buckets, or COALESCE results, which is common in reporting queries.
• Gotcha: Does COUNT(column) count NULL values? No. It counts only non-NULL values, so it can be smaller than COUNT(*) even inside the same group.
• Gotcha: Can I use WHERE SUM(amount) > 100? No. Aggregate results do not exist yet in WHERE; the correct clause is HAVING.
• Gotcha: If I group by one column, can I still project another random column? Not safely. If the column is not grouped or aggregated, the value is undefined in standard SQL and may be rejected by the database.

Common Mistakes:

• Using WHERE for group totals. Correction: use HAVING when the condition depends on an aggregate result.
• Assuming GROUP BY sorts results. Correction: add ORDER BY if you care about the display order.
• Forgetting NULL behavior. Correction: remember that grouping puts NULLs together, and COUNT(column) skips them.
• Selecting extra columns without grouping them. Correction: every selected column must be grouped or aggregated in standard SQL.

Memory Hook: Think of GROUP BY as a shelf sorter: first pile identical items together, then count or sum each pile. One pile, one answer.

Cheat Sheet:

• GROUP BY = one row per group.
• WHERE filters before grouping.
• HAVING filters after grouping.
• COUNT(*) counts rows; COUNT(col) ignores NULL.
• NULLs in the key form one group together.
• Add ORDER BY if you need stable output order.

Practice Tasks:

• Group a orders table by status and count how many orders are in each status.
• Add HAVING to keep only customers with more than 3 orders.
• Rewrite a grouped query so that NULL regions show up as 'Unknown' using COALESCE.