SQL GROUP BY and HAVING Interview Questions
Last updated 2026-11-19
GROUP BY and HAVING look simple until the interviewer adds NULLs, duplicates or a second condition. The most common questions are WHERE versus HAVING, why a column is not allowed in SELECT, how COUNT(*) differs from COUNT(column), and how to find duplicates or groups meeting a condition. The answers below come with the table, the query and the actual result.
Queries use an orders table with a NULL amount and a refunded order, plus the employees table for department-level questions. They are standard SQL and run on MySQL and PostgreSQL unless a difference is noted. After this page, continue to SQL window functions and SQL query practice.
| Tables | orders (7 rows), employees (7 rows) |
|---|---|
| Topics | Aggregates, HAVING, NULLs, duplicates, ROLLUP |
| Dialects | MySQL and PostgreSQL differences noted |
Sample data
The orders table has one NULL amount (order 5, still pending) and one refunded order. Those two rows drive several of the answers.
CREATE TABLE orders (order_id INT, customer VARCHAR(10), amount INT, status VARCHAR(10), order_date DATE);
INSERT INTO orders VALUES (1,'Ravi',500,'paid','2026-01-05'),(2,'Ravi',700,'paid','2026-01-20'),(3,'Sana',300,'refunded','2026-01-22'),
(4,'Sana',900,'paid','2026-02-03'),(5,'Tej',NULL,'pending','2026-02-10'),(6,'Tej',200,'paid','2026-02-11'),(7,'Uma',1200,'paid','2026-02-25');The evaluation order to memorise
- FROM and JOIN, then WHERE (filters rows), then GROUP BY (forms groups).
- HAVING filters groups, after aggregation.
- SELECT is evaluated next, which is why a SELECT alias works in ORDER BY but not in WHERE.
- ORDER BY and LIMIT come last.
SQL GROUP BY and HAVING Interview Questions: questions with worked answers
1 What is the difference between WHERE and HAVING?
easyWHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after aggregation and can use aggregates. Put row-level conditions in WHERE, since they reduce the work for GROUP BY; use HAVING only for conditions on aggregates. This query counts paid orders per customer and keeps customers with at least two.
sqlSELECT customer, COUNT(*) AS paid_orders, SUM(amount) AS total FROM orders WHERE status = 'paid' GROUP BY customer HAVING COUNT(*) >= 2 ORDER BY customer; -- customer | paid_orders | total -- ---------+-------------+------ -- Ravi | 2 | 12002 What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
easyCOUNT(*) counts rows, including rows full of NULLs. COUNT(column) counts rows where the column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values. The NULL amount of order 5 is the reason the second number is 6 instead of 7.
sqlSELECT COUNT(*) AS all_rows, COUNT(amount) AS non_null_amounts, COUNT(DISTINCT customer) AS customers FROM orders; -- all_rows | non_null_amounts | customers -- ---------+------------------+---------- -- 7 | 6 | 43 How do aggregate functions treat NULL?
mediumSUM, AVG, MIN, MAX and COUNT(column) ignore NULLs. AVG divides by the number of non-NULL values, not by the row count, so average and total over row count differ. SUM of an empty or all-NULL set is NULL, not 0, so wrap it in COALESCE when you need 0. COUNT(*) is the only common aggregate that counts NULL rows.
sqlSELECT AVG(amount) AS avg_ignoring_null, SUM(amount) * 1.0 / COUNT(*) AS total_over_rows, COALESCE(SUM(amount) FILTER (WHERE status = 'none'), 0) AS empty_sum FROM orders; -- FILTER is PostgreSQL and SQLite syntax; in MySQL use SUM(CASE WHEN status = 'none' THEN amount END) -- avg_ignoring_null | total_over_rows | empty_sum -- ------------------+-------------------+---------- -- 633.3333333333334 | 542.8571428571429 | 04 Why does this query fail: SELECT customer, order_date, SUM(amount) FROM orders GROUP BY customer?
easyEvery selected column must be either in GROUP BY or inside an aggregate, because a group has many order dates and the database cannot choose one. PostgreSQL, SQL Server and Oracle reject it. MySQL with ONLY_FULL_GROUP_BY disabled (older defaults) returns an arbitrary date, which is a silent bug, and MySQL 5.7.5 and later enable the check by default. Fix by adding the column to GROUP BY or aggregating it with MIN or MAX.
5 Find duplicate values: customers who appear more than once.
mediumGroup on the column that should be unique and keep groups with COUNT(*) greater than 1. This is the standard duplicate-detection query for emails, phone numbers and primary-key violations.
sqlSELECT customer, COUNT(*) AS n FROM orders GROUP BY customer HAVING COUNT(*) > 1 ORDER BY customer; -- customer | n -- ---------+-- -- Ravi | 2 -- Sana | 2 -- Tej | 26 Find departments whose average salary is above the company average.
mediumThe group filter needs HAVING, and the company average needs a subquery, since an aggregate cannot be compared to another unrelated aggregate in the same query level. Gita is in no department, so exclude NULL departments in WHERE first.
sqlSELECT dept_id, AVG(salary) AS avg_sal FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING AVG(salary) > (SELECT AVG(salary) FROM employees) ORDER BY dept_id; -- dept_id | avg_sal -- --------+-------- -- 20 | 800007 How do you compute conditional counts in one pass (pivot-style)?
mediumUse SUM or COUNT over a CASE expression. This avoids one query per status and runs a single scan. PostgreSQL and SQLite also support the FILTER clause, but CASE works in every dialect.
sqlSELECT customer, SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid, SUM(CASE WHEN status <> 'paid' THEN 1 ELSE 0 END) AS other FROM orders GROUP BY customer ORDER BY customer; -- customer | paid | other -- ---------+------+------ -- Ravi | 2 | 0 -- Sana | 1 | 1 -- Tej | 1 | 1 -- Uma | 1 | 08 Group by month. How do you extract the month in MySQL and PostgreSQL?
mediumGroup by an expression derived from the date. MySQL: DATE_FORMAT(order_date, '%Y-%m') or MONTH(order_date). PostgreSQL: DATE_TRUNC('month', order_date) or TO_CHAR(order_date, 'YYYY-MM'). Include the year, otherwise January of two years merge. The sample below uses SQLite's strftime. Truncating to the start of the month with DATE_TRUNC also keeps the result a real date and sortable.
sqlSELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders, SUM(amount) AS revenue FROM orders GROUP BY strftime('%Y-%m', order_date) ORDER BY month; -- month | orders | revenue -- --------+--------+-------- -- 2026-01 | 3 | 1500 -- 2026-02 | 4 | 23009 Can you use a SELECT alias in GROUP BY, HAVING or WHERE?
mediumIt depends on the dialect, which is why it is a favourite trap. WHERE cannot use aliases in any of them, because it is evaluated before SELECT. GROUP BY allows aliases in MySQL and PostgreSQL but not in standard SQL, SQL Server or Oracle. HAVING allows aliases in MySQL and not in PostgreSQL. ORDER BY allows them everywhere. The portable habit is to repeat the expression or wrap the query in a subquery.
10 Find customers whose total over paid orders exceeds 1,000 and show a grand total using ROLLUP.
hardAggregate per customer with HAVING, and add WITH ROLLUP (MySQL) or GROUP BY ROLLUP(customer) (PostgreSQL) to append a grand total row where the group key is NULL. Use GROUPING() to tell a real NULL from a rollup row. SQLite has no ROLLUP, so the demo shows the customer rows only.
sqlSELECT customer, SUM(amount) AS total FROM orders WHERE status = 'paid' GROUP BY customer HAVING SUM(amount) > 1000 ORDER BY customer; -- PostgreSQL: GROUP BY ROLLUP(customer); MySQL: GROUP BY customer WITH ROLLUP adds a final NULL row with the grand total -- customer | total -- ---------+------ -- Ravi | 1200 -- Uma | 120011 What does GROUP BY do with NULLs?
mediumAll NULL values form a single group, even though NULL does not equal NULL in comparisons. So grouping the employees by dept_id gives one group for Gita whose key is NULL. DISTINCT behaves the same way. This is one of the places where NULLs are treated as "not distinct" from each other.
sqlSELECT dept_id, COUNT(*) AS n FROM employees GROUP BY dept_id ORDER BY dept_id; -- dept_id | n -- --------+-- -- NULL | 1 -- 10 | 2 -- 20 | 3 -- 30 | 112 Return the department with the highest total salary, handling ties.
hardAggregate, then compare each total with the maximum total. LIMIT 1 returns only one row even if two departments tie, so the subquery form or a window function RANK is safer. Here Engineering totals 240000 and wins outright.
sqlSELECT dept_id, SUM(salary) AS total FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING SUM(salary) = (SELECT MAX(t) FROM (SELECT SUM(salary) AS t FROM employees WHERE dept_id IS NOT NULL GROUP BY dept_id) x); -- dept_id | total -- --------+------- -- 20 | 24000013 Does GROUP BY guarantee ordering of the result?
mediumNo. MySQL before 8.0 often returned groups sorted as a side effect, and that behaviour was removed in 8.0. PostgreSQL may use hash aggregation and return groups in any order. If order matters, always add ORDER BY. Relying on accidental ordering is a classic production bug when the plan changes.
Frequently asked questions
Can I use HAVING without GROUP BY?
Yes. The whole table is treated as one group, so HAVING COUNT(*) > 5 returns the aggregate row or nothing. It is rarely useful.
Is it faster to filter in WHERE or HAVING?
WHERE, because it removes rows before grouping. Move any condition that does not use an aggregate out of HAVING.
What is the difference between DISTINCT and GROUP BY?
For unique values they return the same rows. GROUP BY is needed when you also compute aggregates.
What should I revise next?
Joins with aggregates in SQL joins questions, then subqueries.
Related guides
More in Programming Language Interview Questions by Language
- SQL Indexes Interview Questions
- SQL Interview Questions and Answers
- SQL Interview Questions for Freshers
- SQL Joins Interview Questions
- SQL Query Practice: Interview Questions with Solutions
- SQL Subqueries Interview Questions
- SQL Window Functions Interview Questions
- TypeScript Interview Questions and Answers
- .NET Core and .NET 8 Interview Questions
- Advanced Python Interview Questions
- ASP.NET Core MVC Interview Questions
- C Language Interview Questions and Answers