SQL GROUP BY, HAVING and Subqueries with Examples

Most questions asked of a database are summaries: how many students per branch, the average package per company, the top scorer in each section. SQL answers them with aggregate functions, GROUP BY and HAVING, and answers "compared with what?" with subqueries and window functions. This note works all of them on one small table, shows exact results (checked on MySQL 8), solves the "second highest salary" question three ways, and ends with the logical order in which SQL evaluates a query, which explains most of the errors you will meet.

The example tables

employees (Neha's salary is not yet set):

departments: Engineering in Bengaluru, Sales in Mumbai, HR in Mumbai, and Legal in Delhi (Legal has no employees).

Aggregate functions and NULLs

An aggregate function turns many rows into one value. Every aggregate except COUNT(*) ignores NULLs:

Whether NULL should count as zero is a business decision, and AVG silently decides it for you. Two edge cases: over zero rows COUNT returns 0 but SUM, AVG, MIN and MAX return NULL. MySQL prints the average of integers with four decimal places.

GROUP BY

GROUP BY splits the rows into groups with equal values in the listed columns, and each aggregate is computed per group:

SELECT dept,
       COUNT(*)      AS staff,
       COUNT(salary) AS paid,
       SUM(salary)   AS total,
       AVG(salary)   AS avg_salary
FROM employees
GROUP BY dept
ORDER BY dept;

1. The 7 rows as the table stores them, departments mixed. GROUP BY dept will gather the rows that share a dept value into one group each.
2. Rows with equal dept values form one group: Engineering (3), HR (2), Sales (2). Nothing is computed yet; each aggregate will run once inside each group.
3. Each group collapses to one row. COUNT(*) counts rows, but COUNT(salary), SUM and AVG skip NULL: HR has 2 staff, 1 paid, and its average is 50000 / 1, not / 2.

The rule: every column in the SELECT list must either appear in GROUP BY or be inside an aggregate. SELECT dept, name, MAX(salary) … GROUP BY dept is ambiguous (which name?), and MySQL 8, whose default sql_mode includes ONLY_FULL_GROUP_BY, rejects it with error 1055. Older MySQL versions returned an arbitrary name, which is why old answers online sometimes "work". All NULLs in a grouping column form one group.

HAVING vs WHERE

HAVING filters groups, so it can test aggregates:

SELECT dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept
HAVING AVG(salary) > 60000;

Result: Engineering 85000.0000 and Sales 62500.0000; HR (50000) is dropped. Combining both filters shows the order in which they run:

SELECT dept, COUNT(*) AS high_earners
FROM employees
WHERE salary > 60000
GROUP BY dept
HAVING COUNT(*) >= 2;

1. WHERE runs first, on single rows: 4 rows have a salary over 60000. Rohan's 60000 is not greater, and Neha's NULL gives UNKNOWN, which WHERE drops like FALSE.
2. GROUP BY sees only the 4 survivors: Engineering with 3 and Sales with 1. HR has no group at all, because WHERE removed both of its rows before grouping began.
3. HAVING runs last, on whole groups, so it may test COUNT(*). Engineering's 3 passes and Sales's 1 fails the test >= 2: the result is one row, Engineering, 3.

The result is one row, Engineering, 3.

Subqueries

A subquery is a query inside another query. It can return one value, a list, or a table.

Scalar subquery (one value): employees paid more than the company average of 71666.67.

SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Result: Asha 90000, Vikram 75000, Meera 90000.

IN subquery (a list): employees in Mumbai departments. WHERE dept IN (SELECT dept FROM departments WHERE city = 'Mumbai') returns Rohan, Priya, Kabir and Neha.

EXISTS (is there at least one row?): departments with someone earning over 80000.

SELECT d.dept FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e
              WHERE e.dept = d.dept AND e.salary > 80000);

Result: Engineering. With NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept = d.dept) the result is Legal, the department with no employees. EXISTS only checks whether a row exists, so SELECT 1 is the convention. Prefer NOT EXISTS to NOT IN whenever the subquery's column can be NULL; the joins note shows NOT IN returning nothing because of one NULL.

Correlated subquery (refers to the outer row): employees who earn more than their own department's average.

SELECT e.name, e.dept, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(x.salary) FROM employees x WHERE x.dept = e.dept);

1. The subquery refers to e.dept, the outer row's department, so it is evaluated again for each outer row. For Asha, it averages Engineering: 85000. 90000 > 85000 is TRUE, so Asha is kept.
2. Vikram: Engineering's average is 85000, computed again for this row. 75000 > 85000 is FALSE, so Vikram is dropped.
3. Meera: Engineering's average is 85000, computed again for this row. 90000 > 85000 is TRUE, so Meera is kept.
4. Rohan: Sales's average is 62500. 60000 > 62500 is FALSE, so Rohan is dropped.
5. Priya: Sales's average is 62500, computed again for this row. 65000 > 62500 is TRUE, so Priya is kept.
6. Kabir earns 50000 and HR's average is 50000 (the NULL salary is skipped): equal is not greater, so the row is dropped.
7. Neha's salary is NULL, so NULL > 50000 is UNKNOWN and the row is dropped, even though the subquery itself ran and returned 50000.
8. 3 rows survive: Asha, Meera, Priya. Logically the subquery ran 7 times for only 3 different answers, which is why optimizers often rewrite it as a join with each department's average computed once.

Changing > to = and AVG to MAX gives the top earners per department: Asha and Meera (tied), Priya and Kabir.

Window functions

A window function computes a value for each row from a set of related rows (its window) without collapsing them the way GROUP BY does. OVER (PARTITION BY … ORDER BY …) defines the window. MySQL supports them from version 8.0.

The three ranking functions differ only on ties:

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS row_num,
       RANK()       OVER (ORDER BY salary DESC)         AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC)         AS dense_rnk
FROM employees
WHERE salary IS NOT NULL;

ROW_NUMBER never ties (emp_id breaks the tie so the numbering is repeatable), RANK leaves a gap after a tie (1, 1, 3), and DENSE_RANK does not (1, 1, 2). Had Neha's row been included, MySQL would place the NULL last in descending order with rank 7 and dense rank 6.

PARTITION BY restarts the window for each group. RANK() OVER (PARTITION BY dept ORDER BY salary DESC) ranks Asha and Meera 1 and Vikram 3 in Engineering, Priya 1 and Rohan 2 in Sales, and Kabir 1 in HR. To keep exactly one top earner per department, number the rows and filter in an outer query, because window functions cannot appear in WHERE:

SELECT dept, name, salary
FROM (SELECT dept, name, salary,
             ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, emp_id) AS rn
      FROM employees) t
WHERE rn = 1;

Result: Engineering Asha 90000, HR Kabir 50000, Sales Priya 65000. Ordinary aggregates also work as windows: AVG(salary) OVER (PARTITION BY dept) puts each department's average beside every row (85000 on each Engineering row).

Second and Nth highest salary, three ways

The salaries in descending order are 90000, 90000, 75000, 65000, 60000, 50000 and NULL, so the second highest distinct salary is 75000.

-- 1. MAX below the MAX
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- 2. Sort distinct salaries and skip one
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- 3. DENSE_RANK
SELECT DISTINCT salary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
      FROM employees
      WHERE salary IS NOT NULL) t
WHERE rnk = 2;

1. ORDER BY salary DESC LIMIT 1 OFFSET 1 skips one row and returns the next. Without DISTINCT the tie at the top fills two rows, so the "second highest" comes back as 90000, the highest again.
2. SELECT DISTINCT salary keeps one copy of each value before the sort, so OFFSET 1 lands on 75000, the second highest distinct salary. The NULL is kept too, and sorts last.
3. With the NULL filtered out, DENSE_RANK numbers the distinct values 1, 2, 3 with no gaps, so rank 2 is 75000. RANK skips a number after the tie, 1, 1, 3, so no row has rank 2 and the query would return nothing.

All three return 75000. How they generalize to the Nth highest:

A fourth classic uses a correlated count: keep the salary that exactly N−1 distinct salaries exceed, WHERE N - 1 = (SELECT COUNT(DISTINCT x.salary) FROM employees x WHERE x.salary > e.salary). MySQL does not accept an expression such as N - 1 directly after OFFSET, so for a variable N use a prepared statement or the DENSE_RANK form.

Logical order of SQL clauses

You write SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT, but the DBMS evaluates the clauses in a different logical order:

Window functions are computed in the SELECT step, so they see only the rows that survived WHERE and HAVING, and they cannot be used in WHERE either. The optimizer may physically reorder work, but never in a way that changes this result.

Common mistakes

• Expecting AVG to count NULLs as zero: it ignores them.
• Selecting a column that is neither grouped nor aggregated: an error under ONLY_FULL_GROUP_BY, a random value in old MySQL.
• Filtering an aggregate in WHERE instead of HAVING.
• Using a window function in WHERE: compute it in a derived table and filter outside.
• Forgetting DISTINCT in the LIMIT … OFFSET answer to the second highest salary.
• Using RANK where DENSE_RANK is meant for "Nth highest".

Interview questions

**What does SELECT COUNT(*) FROM employees GROUP BY dept return for an empty table?** No rows. With no rows there are no groups, whereas SELECT COUNT(*) FROM employees without GROUP BY returns one row holding 0.

Can you use HAVING without GROUP BY? Yes. The whole result is then one group, so SELECT COUNT(*) FROM employees HAVING COUNT(*) > 5 returns one row (7) or none.

What is the difference between a correlated and a non-correlated subquery? A non-correlated subquery does not refer to the outer query and is evaluated once. A correlated one refers to the outer row and is logically evaluated per outer row, though optimizers often turn it into a join.

IN or EXISTS: which should you use? They give the same result for positive tests, and optimizers often run both as a semi-join. For negative tests use NOT EXISTS, because NOT IN returns nothing if the subquery yields a NULL.

How do you find the highest-paid employee in each department? Either a correlated subquery comparing salary with the department's MAX, which returns all tied employees, or ROW_NUMBER (or RANK) partitioned by department and filtered to 1 in an outer query.

Write a query for departments with more than one employee. SELECT dept, COUNT(*) FROM employees GROUP BY dept HAVING COUNT(*) > 1. Here it returns Engineering 3, HR 2 and Sales 2.

What is the difference between GROUP BY and a window function's PARTITION BY? GROUP BY returns one row per group. PARTITION BY keeps every row and attaches the per-group value to each, so you can show a salary beside its department average.

In what order is a SELECT statement evaluated? FROM and joins, WHERE, GROUP BY, HAVING, SELECT (including window functions), DISTINCT, ORDER BY, LIMIT. Most "unknown column" and "invalid use of group function" errors follow from this order.

Next, make several statements succeed or fail together: Transactions and ACID Properties in DBMS. Window functions and subqueries are examined in the SQL (Intermediate) skill test.

Practise on real tables: Second-Best Innings in the College Cup (the "second highest" problem), Quiz Team Standings With Shared Places (DENSE_RANK) and Students Who Attended Every Fest Workshop (HAVING with a subquery).