0Pricing
SQL Interview Prep · Lesson

Per-Group Aggregates Without GROUP BY

Using a correlated subquery to compute a group max alongside detail rows.

Per-Group Aggregates Without GROUP BY is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Detail-Plus-Aggregate Problem

A staple interview ask: "Show every row alongside an aggregate of its group." For example, list each employee with their department's maximum salary on the same line.

A plain GROUP BY collapses rows, so it cannot keep the per-employee detail. You need the detail rows and a group-level number together.

A correlated subquery solves this elegantly: it computes the group aggregate for each detail row without collapsing anything.

Why Plain GROUP BY Fails Here

If you write SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id, you get one row per department, losing individual names.

Adding name to the SELECT without adding it to GROUP BY raises the classic "column must appear in GROUP BY" error.

The interviewer is checking whether you understand that GROUP BY reduces cardinality. To keep detail rows, you compute the aggregate a different way.

Correlated Subquery to the Rescue

Place the group aggregate in the SELECT list as a correlated subquery. Each employee row triggers an inner MAX scoped to that employee's department.

The correlation e2.dept_id = e1.dept_id ties the aggregate to the right group while the outer query still returns one row per employee.

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT MAX(e2.salary)
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id) AS dept_max_salary
FROM employees e1;

Comparing Each Row to Its Group

Once the group aggregate sits in the query, you can compare each row to it. A frequent question: "Find employees earning above their department average."

Here the correlated AVG is in WHERE, so each employee is tested against their own department's mean.

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

Computing a Difference From the Group

You can also show how far each row sits from its group aggregate. Subtracting the correlated average gives a per-row gap.

Notice the same correlated subquery can be reused in multiple SELECT expressions; the engine evaluates it per row each time it appears.

SELECT e1.name,
       e1.salary,
       e1.salary - (SELECT AVG(e2.salary)
                    FROM employees e2
                    WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;

Finding the Top Earner Per Group

To return only the highest-paid person per department, compare each salary to the correlated MAX and keep matches.

This pattern returns ties: if two employees share the department max, both appear. That ties-handling behavior is often the interviewer's follow-up question.

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

The Window Function Alternative

Modern SQL offers a cleaner tool: window functions. MAX(salary) OVER (PARTITION BY dept_id) computes the group aggregate without collapsing rows and without a correlated re-scan.

Interviewers love when you can produce both solutions and explain that the window version usually performs better because it scans the table once.

SELECT name,
       dept_id,
       salary,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;

Correlated vs Window Trade-offs

Both approaches return the same shape, but they differ:

  • Correlated subquery: portable, works on very old engines, but re-evaluates per row.
  • Window function: single pass, far faster on large tables, requires SQL window support.

Say which one you would pick and why. For a one-off on a small table either is fine; for analytics at scale, prefer the window function.

Worked Example: Orders Above Customer Average

Apply the pattern to orders. Show orders whose amount beats the placing customer's own average order value.

The correlated AVG is scoped by o2.customer_id = o1.customer_id, giving each order its customer's personal baseline.

SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

Watch the NULL and Empty-Group Cases

If a group has only one row, its average equals that row, so salary > avg is false and the row drops out. Mention this edge case proactively.

Also, NULL salaries are ignored by AVG and MAX, matching SQL aggregate semantics. If every value in a group is NULL, the aggregate is NULL and comparisons become UNKNOWN, excluding the row. Anticipating these is what separates a thorough answer.

Counting Rank Within a Group

You can express a row's rank inside its group with a correlated COUNT. To find each employee's salary rank within their department, count how many peers earn more.

Rank 1 means top earner. Adding 1 turns the count of higher earners into a 1-based position, and the correlation keeps it department-scoped.

SELECT e1.name,
       e1.dept_id,
       e1.salary,
       (SELECT COUNT(*) + 1
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id
          AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;

Quick Check

Pick the reason a correlated subquery beats a plain GROUP BY for this task.

Recap: Per-Group Aggregates Without GROUP BY

Key takeaways:

  • A correlated subquery puts a group-level aggregate on every detail row without collapsing them.
  • Use it in SELECT to display the aggregate, or in WHERE to compare each row to its group.
  • The = MAX(...) pattern returns all tied top rows.
  • A window function with PARTITION BY does the same in one pass and usually scales better.

Offer both solutions and justify your pick in the interview.

Frequently asked questions

Is the “Per-Group Aggregates Without GROUP BY” lesson free?

Yes — the full text of “Per-Group Aggregates Without GROUP BY” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Per-Group Aggregates Without GROUP BY”?

Using a correlated subquery to compute a group max alongside detail rows. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Per-Group Aggregates Without GROUP BY” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Anatomy of a Correlated Subquery
  2. Per-Group Aggregates Without GROUP BY
  3. Correlated EXISTS and NOT EXISTS
  4. Rewriting Correlated Subqueries as Joins
← Back to SQL Interview Prep