0Pricing
SQL Interview Prep · Lesson

The GROUP BY Rule for SELECT Columns

Why every non-aggregated column must appear in GROUP BY and the only-full-group-by mode.

The GROUP BY Rule for SELECT Columns is a free SQL Interview Prep lesson on CoddyKit — lesson 1 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.

Why Interviewers Start With GROUP BY

GROUP BY is where interviews separate juniors from mid-levels. The single rule they test most: every column in your SELECT list must either be inside an aggregate function or be listed in GROUP BY.

If you break this rule, the engine cannot decide which value to show for a group that has many rows. Interviewers plant this exact mistake to see if you understand what a group really is.

What a Group Actually Is

GROUP BY collapses many rows into one row per distinct key. After grouping, the engine no longer has individual rows. It only has one summary row per group.

  • Columns you grouped by have one clear value per group.
  • Aggregates like COUNT, SUM, AVG reduce the many values down to one.
  • Any other raw column is ambiguous: which of the many values should appear?
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

The Classic Error

Here is the bug interviewers love. You group by department but also select name, a non-aggregated column that is not in GROUP BY.

Each department has many employees, so there are many names per group. The engine cannot pick one, so standard SQL rejects the query.

-- ERROR: name is not in GROUP BY and not aggregated
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

Two Ways to Fix It

You have two legitimate fixes, and the interviewer wants to hear that you know both produce different answers:

  • Add the column to GROUP BY if you genuinely want a finer grouping (one row per department and name).
  • Wrap it in an aggregate like MAX(name) or COUNT(name) if you want one value per existing group.
-- Finer grouping
SELECT department, name, COUNT(*) AS rows_for_person
FROM employees
GROUP BY department, name;

ONLY_FULL_GROUP_BY in MySQL

A favorite trap: older MySQL allowed selecting non-grouped columns and silently returned an arbitrary value from the group. This produced wrong reports that looked fine.

Modern MySQL enables ONLY_FULL_GROUP_BY by default, which enforces the standard rule. Postgres, SQL Server, and Oracle have always enforced it. If asked why a query 'worked on the old server but breaks now,' this is the answer.

-- Legal under ONLY_FULL_GROUP_BY because every
-- selected column is grouped or aggregated
SELECT department, MAX(hire_date) AS latest_hire
FROM employees
GROUP BY department;

Functional Dependency Exception

There is a nuance interviewers use to test depth. If you group by a table's primary key, then every other column of that table is functionally dependent on the key, so it has exactly one value per group.

Postgres and modern MySQL allow selecting those dependent columns without listing them. The grouped key uniquely determines them, so there is no ambiguity.

-- Legal: id is the PK, so name is determined by it
SELECT e.id, e.name, COUNT(o.id) AS orders
FROM employees e
LEFT JOIN orders o ON o.employee_id = e.id
GROUP BY e.id;

Worked Example: Sales by Region

Suppose you must report total sales per region. The grouping key is region; the measure is SUM(amount). Everything else must be aggregated or dropped.

Notice how clean this is: one row per region, each carrying a single summed value. This is the shape every aggregate report takes.

SELECT region,
       SUM(amount)   AS total_sales,
       COUNT(*)      AS num_orders,
       AVG(amount)   AS avg_order
FROM sales
GROUP BY region;

Mixing Detail and Summary

A trick prompt: 'Show each order's amount alongside its region total.' You cannot do this with plain GROUP BY, because grouping destroys the individual rows.

The correct answer is a window function (SUM(amount) OVER (PARTITION BY region)) or a join back to a grouped subquery. Recognizing that GROUP BY is the wrong tool here is the point of the question.

-- Detail rows kept, region total added per row
SELECT order_id, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales;

GROUP BY and SELECT Aliases

Can you GROUP BY an alias defined in SELECT? It depends on the dialect, and that inconsistency is exactly what interviewers probe.

  • MySQL and Postgres: allow grouping by a SELECT alias.
  • SQL Server and Oracle: do not; you must repeat the full expression.

The portable answer is to repeat the expression in GROUP BY, which works everywhere.

-- Portable: repeat the expression rather than the alias
SELECT EXTRACT(YEAR FROM order_date) AS yr, COUNT(*)
FROM sales
GROUP BY EXTRACT(YEAR FROM order_date);

Distinct vs Group By for Uniqueness

If you only want distinct combinations and no aggregate, GROUP BY with no aggregate behaves like DISTINCT. Interviewers may ask which is clearer.

Use DISTINCT to express intent ('I want unique rows'). Reserve GROUP BY for when you also compute aggregates. Same result, different readability signal.

-- These return the same rows
SELECT DISTINCT department, role FROM employees;
SELECT department, role FROM employees GROUP BY department, role;

How to Say It in the Interview

When you hit a GROUP BY question, narrate the rule out loud: 'Every selected column is either a grouping key or wrapped in an aggregate, because grouping leaves one row per key.'

Then state your grouping key, your measures, and confirm nothing leaks through ungrouped. That structured answer signals mid-level competence even before you write the query.

Quick Check

Test your grasp of the core GROUP BY rule.

Recap

The rule: every SELECT column is a grouping key or an aggregate. Why: GROUP BY leaves one row per key, so ungrouped raw columns are ambiguous.

  • Fix violations by grouping the column or aggregating it.
  • MySQL's old behavior returned arbitrary values; ONLY_FULL_GROUP_BY enforces the standard.
  • Primary-key functional dependency is the one legal exception.
  • To keep detail rows beside group totals, use window functions, not GROUP BY.

Frequently asked questions

Is the “The GROUP BY Rule for SELECT Columns” lesson free?

Yes — the full text of “The GROUP BY Rule for SELECT Columns” 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 “The GROUP BY Rule for SELECT Columns”?

Why every non-aggregated column must appear in GROUP BY and the only-full-group-by mode. 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 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “The GROUP BY Rule for SELECT Columns” 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. The GROUP BY Rule for SELECT Columns
  2. HAVING vs WHERE
  3. Grouping by Multiple Columns and Expressions
  4. Counting and Filtering Groups
← Back to SQL Interview Prep