0Pricing
SQL Interview Prep · Lesson

Aggregates Without GROUP BY

How a bare aggregate collapses a whole table into one row.

Aggregates Without GROUP BY is a free SQL Interview Prep lesson on CoddyKit — lesson 4 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.

One Aggregate, One Row

Interviewers test a foundational idea with this question: "What happens when you use an aggregate function without GROUP BY?"

The answer: the entire table collapses into a single row. The aggregate treats all qualifying rows as one big implicit group. Understanding this "implicit group" model explains many downstream rules, so let's build it carefully.

The Implicit Single Group

When you write a bare aggregate with no GROUP BY, SQL behaves as if there is one group containing every row. The result is always exactly one row, no matter how many rows the table has — even if the table is empty.

So SELECT COUNT(*) FROM employees returns one row, with one number, summarizing the whole table.

SELECT COUNT(*) AS total_employees
FROM employees;
-- always returns exactly one row

Multiple Aggregates Together

You can list several aggregates in one SELECT. They all summarize the same implicit group, so you still get exactly one row with multiple columns.

This is the standard "give me the summary stats" query. All four numbers describe the same full set of rows.

SELECT
  COUNT(*)     AS num_rows,
  SUM(salary)  AS total_pay,
  AVG(salary)  AS avg_pay,
  MAX(salary)  AS top_pay
FROM employees;

The Mixing Error

Here is the most-tested rule. You cannot mix a bare aggregate with a non-aggregated column in the same SELECT without GROUP BY.

Why? The aggregate produces one row, but a plain column has many values — which one would it show? It is ambiguous, so standard SQL raises an error.

-- ERROR: name is not aggregated and there is no GROUP BY
SELECT name, MAX(salary)
FROM employees;

Why It Is Ambiguous

Think it through with our table. MAX(salary) collapses all rows to one value. But name has a different value in every row. The single summary row cannot hold five names in one cell.

The database refuses to guess. This is the same principle behind the GROUP BY rule: every selected column must be either aggregated or part of the group. With no GROUP BY, only aggregates are allowed.

MySQL's Loose Exception

One dialect wrinkle worth naming: older or non-strict MySQL would allow the mixing query and return an arbitrary name — not necessarily the top earner's.

Modern MySQL with ONLY_FULL_GROUP_BY enabled (the default now) rejects it like everyone else. Mentioning this shows cross-dialect awareness, but always treat the mixed query as incorrect.

Getting the Row Behind the Max

So how do you get the name of the highest-paid employee? Not by mixing. Use ORDER BY + LIMIT, or a subquery comparing to the aggregate.

The ORDER BY/LIMIT version is the simplest. The subquery version generalizes nicely and returns all employees tied for the max.

-- Subquery: returns everyone earning the max
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Aggregates on an Empty Table

An edge case interviewers slip in: "What does a bare aggregate return on an empty table?"

  • COUNT(*) returns 0 (one row, value 0).
  • SUM, AVG, MIN, MAX return NULL (one row, NULL value).

Crucially, you still get one row back, never zero rows. That single-row guarantee is the whole point of an aggregate without GROUP BY.

SELECT COUNT(*) AS c, SUM(salary) AS s, MAX(salary) AS m
FROM employees
WHERE 1 = 0;
-- one row: c=0, s=NULL, m=NULL

No WHERE Aggregate of Aggregate

Another rule that follows from the model: you cannot put an aggregate in a WHERE clause, because WHERE filters individual rows before aggregation happens.

To filter on an aggregate result you use HAVING (with grouping) or wrap the aggregate in a subquery. WHERE simply runs too early in the execution order to see the aggregate.

-- ERROR: aggregate not allowed in WHERE
-- SELECT * FROM employees WHERE salary > AVG(salary);

-- Correct: compare to the aggregate via subquery
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Bare Aggregate vs GROUP BY

Frame the contrast for yourself:

  • No GROUP BY: one implicit group = the whole table = exactly one result row.
  • With GROUP BY col: one group per distinct value of col = one row per group.

A bare aggregate is just GROUP BY with zero grouping columns. Same machinery, one big bucket.

-- Whole table: one row
SELECT AVG(salary) FROM employees;

-- Per department: one row each
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

How to Explain It

A clean answer: "An aggregate without GROUP BY treats the whole result set as one implicit group and returns exactly one summary row, even on an empty table. You can't select a non-aggregated column alongside it because that value would be ambiguous. To get the row behind a MAX or MIN, I use ORDER BY with LIMIT or a subquery comparing to the aggregate."

Quick Check

Predict the output shape.

Recap

Aggregates without GROUP BY in a nutshell:

  • The whole table becomes one implicit group → exactly one result row.
  • You cannot mix a bare aggregate with a non-aggregated column.
  • Empty table: COUNT(*) = 0, others return NULL, still one row.
  • Aggregates cannot appear in WHERE; use a subquery or HAVING.
  • To get the row behind MIN/MAX, use ORDER BY + LIMIT or a subquery.

That completes the aggregate functions interview drills.

Frequently asked questions

Is the “Aggregates Without GROUP BY” lesson free?

Yes — the full text of “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 “Aggregates Without GROUP BY”?

How a bare aggregate collapses a whole table into one row. 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 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “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. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT)
  2. SUM and AVG with NULLs
  3. MIN, MAX and Non-Numeric Aggregation
  4. Aggregates Without GROUP BY
← Back to SQL Interview Prep