Autocoder

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.

Tablesorders (7 rows), employees (7 rows)
TopicsAggregates, HAVING, NULLs, duplicates, ROLLUP
DialectsMySQL 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.

sql
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

SQL GROUP BY and HAVING Interview Questions: questions with worked answers

  1. 1

    What is the difference between WHERE and HAVING?

    easy

    WHERE 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.

    sql
    SELECT 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           | 1200
  2. 2

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

    easy

    COUNT(*) 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.

    sql
    SELECT 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                | 4
  3. 3

    How do aggregate functions treat NULL?

    medium

    SUM, 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.

    sql
    SELECT 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 | 0
  4. 4

    Why does this query fail: SELECT customer, order_date, SUM(amount) FROM orders GROUP BY customer?

    easy

    Every 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. 5

    Find duplicate values: customers who appear more than once.

    medium

    Group 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.

    sql
    SELECT customer, COUNT(*) AS n FROM orders GROUP BY customer HAVING COUNT(*) > 1 ORDER BY customer;
    -- customer | n
    -- ---------+--
    -- Ravi     | 2
    -- Sana     | 2
    -- Tej      | 2
  6. 6

    Find departments whose average salary is above the company average.

    medium

    The 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.

    sql
    SELECT 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      | 80000
  7. 7

    How do you compute conditional counts in one pass (pivot-style)?

    medium

    Use 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.

    sql
    SELECT 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    | 0
  8. 8

    Group by month. How do you extract the month in MySQL and PostgreSQL?

    medium

    Group 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.

    sql
    SELECT 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      | 2300
  9. 9

    Can you use a SELECT alias in GROUP BY, HAVING or WHERE?

    medium

    It 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. 10

    Find customers whose total over paid orders exceeds 1,000 and show a grand total using ROLLUP.

    hard

    Aggregate 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.

    sql
    SELECT 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      | 1200
  11. 11

    What does GROUP BY do with NULLs?

    medium

    All 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.

    sql
    SELECT 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      | 1
  12. 12

    Return the department with the highest total salary, handling ties.

    hard

    Aggregate, 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.

    sql
    SELECT 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      | 240000
  13. 13

    Does GROUP BY guarantee ordering of the result?

    medium

    No. 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

See all in this section