SQL GROUP BY and HAVING interview questions, with answers

The questions, with answers

1. 1.What is the difference between WHERE and HAVING?

   WHERE filters individual rows before they are grouped, so it cannot reference an aggregate — there are no groups yet to aggregate over. HAVING filters after GROUP BY has formed the groups, so it is where SUM, COUNT and AVG conditions live. A condition that only involves plain columns belongs in WHERE: it removes rows earlier, which is cheaper, and it changes what the aggregates see.

   The interview follow-up: "orders over 100" in WHERE keeps only large orders and then totals them; "total over 100" in HAVING totals every order and then keeps only big customers. Same words, different questions.

   -- customers whose 2024 spend exceeds 10000
   SELECT customer_id, SUM(amount) AS total
   FROM Orders
   WHERE order_year = 2024        -- rows, before grouping
   GROUP BY customer_id
   HAVING SUM(amount) > 10000;    -- groups, after grouping

2. 2.Why does the database complain that a column "must appear in the GROUP BY clause"?

   Because once rows are grouped, each output row stands for many input rows, and a column that is neither grouped nor aggregated has no single value to show. Standard SQL and SQL Server reject the query; PostgreSQL does too, except when you group by the table's primary key, since the other columns are then determined by it. MySQL used to pick an arbitrary value silently — a famous source of wrong reports — but since 5.7.5 the ONLY_FULL_GROUP_BY mode is on by default and it rejects the query as well, with the same functional-dependence exception.

   The fix is to add the column to GROUP BY, wrap it in an aggregate such as MAX, or group by the key it depends on.

3. 3.What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

   COUNT(*) counts rows in the group, NULLs and all. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values. On a Sales table with five rows where two have a NULL discount and two others share the same discount value, COUNT(*) is 5, COUNT(discount) is 3, and COUNT(DISTINCT discount) is 2.

   The practical difference shows up after a LEFT JOIN: COUNT(*) counts the preserved left row even when nothing matched, while COUNT(right_table.id) counts only real matches.

4. 4.How do you find duplicate rows with GROUP BY?

   Group by the columns that should be unique and keep the groups with more than one member. That returns each duplicated value once, with how many times it appears — exactly what a data-cleanup question wants. To see the offending rows themselves, join back to the table on those columns, or use a window function COUNT(*) OVER (PARTITION BY email) and filter on it in an outer query.

   SELECT email, COUNT(*) AS copies
   FROM Users
   GROUP BY email
   HAVING COUNT(*) > 1;

5. 5.In what order are WHERE, GROUP BY, HAVING, SELECT, ORDER BY and LIMIT evaluated?

   Logically: FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then the SELECT list, then DISTINCT, then ORDER BY, then LIMIT or OFFSET. The order explains most error messages. A column alias defined in SELECT can be used in ORDER BY (it runs later) but not in WHERE or, in standard SQL, in HAVING (they run earlier) — MySQL allows the alias in HAVING as an extension, PostgreSQL does not. And LIMIT runs last, so "top three customers by spend" is GROUP BY, then ORDER BY total DESC, then LIMIT 3 — the limit never cuts rows before the totals are computed.

   SELECT customer_id, SUM(amount) AS total
   FROM Orders
   GROUP BY customer_id
   ORDER BY total DESC     -- alias is fine here
   LIMIT 3;

6. 6.How does GROUP BY treat NULL values?

   All the NULLs go into one group. That surprises people because NULL never equals NULL in a comparison, yet GROUP BY (like DISTINCT) treats NULLs as the same for the purpose of grouping. So grouping orders by a nullable coupon_code gives you one row per code plus one row where coupon_code is NULL — the orders with no coupon. If you don't want that row, filter it out in WHERE with IS NOT NULL; if you want it labelled, wrap the column in COALESCE(coupon_code, 'none') and group by that expression.

7. 7.Can you use HAVING without GROUP BY?

   Yes. With no GROUP BY, the whole result set is a single group, so HAVING COUNT(*) > 100 returns one row if the table has more than a hundred matching rows and no rows otherwise. It is a legitimate way to write "only report the total if there is enough data". What you cannot do is reference a non-aggregated column in that HAVING, for the same reason as always — there is one output row standing for every input row. MySQL will also accept a HAVING with no aggregate at all, behaving like a late WHERE, but that is a MySQL extension and it is slower than a real WHERE; don't offer it in an interview.

8. 8.How do you count per customer without losing customers who have no orders?

   Start from Customers, LEFT JOIN Orders, and count a column from the orders side. Because the join preserves every customer, a customer with no orders appears with NULLs in the order columns; COUNT(o.id) sees those NULLs and returns 0 for them. COUNT(*) would return 1 — it counts the preserved row — which is the classic wrong answer. The same idea applies to SUM: SUM(o.amount) is NULL for a customer with no orders, so wrap it in COALESCE if you want 0.

   SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.amount), 0) AS spend
   FROM Customers c
   LEFT JOIN Orders o ON o.customer_id = c.id
   GROUP BY c.id, c.name;

How the diagnostic asks it

One question from the SQL bank, exactly as a sitting would show it. The bank has 5 on group by & having and 60 across SQL.