SQL Quiz: Avoiding Non Aggregated Columns
10 questions · exam conditions
0:00
Avoiding Non Aggregated ColumnsQuestion 1 of 10

A payroll report must return exactly one row for each department and job title, showing only the average salary for that group. The database enforces the rule that every selected non-aggregated expression must be included in the GROUP BY clause.

The original query selects department_id, job_title, employee_name, and AVG(salary), but groups only by department_id and job_title. Which revision best satisfies the report requirement?

Add employee_name to GROUP BY, while leaving all selected columns unchanged.
Replace employee_name with MAX(employee_name), while retaining the existing grouping.
Remove employee_name from SELECT, while retaining the existing grouping.
Add DISTINCT after SELECT, while retaining the existing selected columns.
← Back to quizzes

SQL Quiz

SQL Quiz: Avoiding Non Aggregated Columns

Practice Avoiding Non Aggregated Columns in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Avoiding Non Aggregated Columns, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A payroll report must return exactly one row for each department and job title, showing only the average salary for that group. The database enforces the rule that every selected non-aggregated expression must be included in the GROUP BY clause.

The original query selects department_id, job_title, employee_name, and AVG(salary), but groups only by department_id and job_title. Which revision best satisfies the report requirement?

  1. Add employee_name to GROUP BY, while leaving all selected columns unchanged.
  2. Replace employee_name with MAX(employee_name), while retaining the existing grouping.
  3. Remove employee_name from SELECT, while retaining the existing grouping. (correct answer)
  4. Add DISTINCT after SELECT, while retaining the existing selected columns.
Explanation: Whenever you see a GROUP BY question, ask yourself: does every non-aggregated column in my SELECT list appear in the GROUP BY clause? This is the foundational rule SQL enforces to ensure each output row represents a well-defined group, not an ambiguous mix of values. Here, the report goal is one row per department-and-job-title combination, showing only the average salary. The problem is employee_name — it's selected but neither aggregated nor included in GROUP BY. This makes the query invalid and, more importantly, meaningless for the report: a single employee name can't logically represent an entire group. The cleanest fix is simply removing employee_name from SELECT, which is exactly what C does. The grouping stays intact, the aggregation works correctly, and the output matches the stated requirement perfectly. A is tempting but wrong. Adding employee_name to GROUP BY fixes the syntax error, but it fundamentally changes the query's behavior — you'd now get one row per employee, not per department-and-job-title pair, defeating the entire purpose of the report. B wrapping employee_name in MAX() satisfies the database's syntax rule, but it still returns a column (the alphabetically last employee name) that the report doesn't need and never asked for. It's a workaround, not a solution. D adding DISTINCT doesn't resolve the core issue. DISTINCT removes duplicate rows in the result set but does nothing to fix a non-aggregated column missing from GROUP BY — the query would still fail. Study tip: When debugging a GROUP BY error, audit your SELECT list column by column — every item must either be in GROUP BY or wrapped in an aggregate function like AVG(), MAX(), or COUNT().

Question 2

Consider this query on a strict SQL database: SELECT state_code, category, SUM(amount) FROM sales WHERE state_code = 'CA' GROUP BY category;

Which statement correctly describes the query and an appropriate correction?

  1. The query is valid because the WHERE clause guarantees that state_code has only one value.
  2. The query is invalid; adding state_code to GROUP BY makes the selected column legal without changing this filtered result's practical grain. (correct answer)
  3. The query is invalid; replacing GROUP BY category with ORDER BY category correctly preserves the category totals.
  4. The query is valid only if DISTINCT is added, because that removes repeated occurrences of the constant state code.
Explanation: Whenever you see a SELECT list mixed with a GROUP BY clause, ask yourself: can every selected column be unambiguously reduced to one value per group? In standard SQL, every column in SELECT must either appear in GROUP BY or be wrapped in an aggregate function. This rule exists because the database engine needs to know how to collapse multiple rows into one output row. In this query, state_code is selected but absent from GROUP BY category. Even though the WHERE clause filters rows to only 'CA', a strict SQL engine evaluates the SELECT list against the grouping rules syntactically, before considering what values actually exist at runtime. So the query fails validation. The fix — adding state_code to GROUP BY — makes the column legally grouped. Because the WHERE already limits state_code to one distinct value, the extra grouping column doesn't split any groups or change the output rows. The result is identical in practice. That's exactly what B describes, making it correct. A is the classic trap: the logic sounds reasonable, but SQL engines don't reason about runtime data distributions when enforcing grouping rules. A single-value column still must appear in GROUP BY or an aggregate. C is wrong because swapping GROUP BY for ORDER BY would eliminate the aggregation entirely. You'd lose the SUM grouping logic — ORDER BY only sorts rows, it doesn't aggregate them. D is wrong because DISTINCT filters duplicate result rows; it has nothing to do with satisfying the GROUP BY requirement for non-aggregated columns. Study tip: Whenever you select a non-aggregated column, mentally check: "Is this in my GROUP BY?" If not, the query is invalid — regardless of what WHERE filters are applied.

Question 3

An orders table contains channel, status, and one row per order. A report must show every channel, including channels with no paid orders, and the number of paid orders for each channel.

Which query design avoids selecting an ungrouped status value while preserving the required one-row-per-channel result?

  1. Select channel, status, and COUNT(*); group by both channel and status to separate each payment status.
  2. Filter to status = 'paid', then select channel and COUNT(*) grouped by channel so only paid rows remain.
  3. Select channel and MAX(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) grouped by channel.
  4. Select channel and SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) grouped by channel. (correct answer)
Explanation: When a question asks you to count a subset of rows (e.g., only "paid" orders) while still returning one row per group, you need to decide how to isolate that subset without distorting the grouping structure. This is a classic conditional aggregation problem. The cleanest solution is D: SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) grouped by channel. The CASE expression converts each row into a 1 (paid) or 0 (not paid), and SUM adds them up — effectively counting only paid orders. Because you're only grouping by channel, you get exactly one row per channel, and status never appears as an ungrouped column. A groups by both channel and status, which produces multiple rows per channel (one for each distinct status value). That violates the one-row-per-channel requirement, so the result would need further pivoting to be useful. B filters with WHERE status = 'paid' before aggregation. This correctly counts paid orders per channel, but it eliminates channels that have no paid orders entirely — those channels disappear from the result set. Since the requirement says "every channel, including channels with no paid orders," B fails that condition. C uses MAX(CASE WHEN status = 'paid' THEN 1 ELSE 0 END). This returns 1 if any paid order exists for that channel, and 0 otherwise — essentially a flag, not a count. It can't tell you how many paid orders exist. As a general strategy: whenever you need to count or sum a conditional subset without losing rows from your main group, reach for SUM(CASE WHEN ... THEN 1 ELSE 0 END) — it's one of the most versatile patterns in SQL aggregation.

Question 4

A report joins products p to categories c and must return one row per category with c.category_id, c.category_name, and SUM(p.revenue). The database does not infer functional dependencies across joins and requires each selected non-aggregate expression to be explicitly grouped.

Which revision most directly makes the query valid while preserving the requested category-level result and avoiding an arbitrary aggregate around the category name?

  1. Group by c.category_id and p.product_id, then continue selecting the category name and summed revenue.
  2. Group by both c.category_id and c.category_name, then calculate the summed product revenue. (correct answer)
  3. Group only by c.category_id, but replace the category name with MAX(c.category_name).
  4. Group only by c.category_name, but continue selecting the unaggregated category identifier.
Explanation: Whenever you see a GROUP BY question involving a join, the core rule to remember is this: every non-aggregate column in your SELECT must appear in your GROUP BY clause. Databases that don't infer functional dependencies require you to be explicit — they won't assume that because category_id uniquely identifies category_name, you can skip grouping on the name. In this query, you're selecting c.category_id, c.category_name, and SUM(p.revenue). The SUM is already aggregated, so it's fine. That leaves c.category_id and c.category_name both needing to appear in GROUP BY. Option B does exactly this — grouping by both columns — which satisfies the database engine, produces one row per category, and keeps the result clean and semantically accurate. Option A adds p.product_id to the GROUP BY, which breaks the category-level aggregation entirely. You'd end up with one row per product, not per category, so the SUM becomes meaningless at the level you need. Option C wraps category_name in MAX(), which technically silences the error but uses an arbitrary aggregate to paper over a grouping problem — the question specifically warns against this approach. Option D groups only by category_name while leaving c.category_id unaggregated in the SELECT, which is the same original error in reverse — the identifier is now the ungrouped column causing the violation. A practical tip: before writing your GROUP BY, scan your SELECT list and circle every column that isn't inside an aggregate function. Those circled columns form your required GROUP BY list — no more, no less than what your intended granularity demands.

Question 5

A developer submits this query to a strict SQL database: SELECT DISTINCT department_id, employee_name, COUNT(*) FROM employees GROUP BY department_id; Several employees may belong to the same department.

What is the correct assessment of this query?

  1. It is invalid because employee_name is neither grouped nor aggregated; DISTINCT does not repair that violation. (correct answer)
  2. It is valid because DISTINCT chooses one employee name from each department before grouping occurs.
  3. It is valid because COUNT(*) converts every selected column into an aggregate result for each department.
  4. It is invalid only when two departments contain employees who happen to have the same employee name.
Explanation: Whenever you see a SELECT list mixed with GROUP BY, apply one simple rule: every column in the SELECT must either appear in the GROUP BY clause or be wrapped in an aggregate function. This rule exists because SQL needs to know exactly which single value to display for each group. In this query, employee_name appears in the SELECT but is absent from the GROUP BY (which only lists department_id). A single department can have many employees, so the database has no logical way to collapse multiple names into one row — it doesn't know which name to show. A strict SQL engine will reject this outright, making A the correct answer. DISTINCT is applied after grouping and aggregation, so it has no power to resolve the ambiguity introduced by an ungrouped, non-aggregated column. B reflects a common misconception that DISTINCT acts as a pre-filter before GROUP BY runs. It doesn't — DISTINCT simply removes duplicate rows from the final result set, and it cannot substitute for proper grouping logic. C is wrong because COUNT(*) only aggregates the row count; it does nothing to collapse employee_name into a single valid value. Each column must be handled individually. D attempts to make the error conditional on data coincidence (same name across departments), but the violation is structural, not data-dependent. The query fails regardless of what names are actually stored. A useful rule of thumb: mentally check every column in your SELECT list and ask "is this in my GROUP BY, or is it inside an aggregate?" If either answer is no, your query is broken.

Question 6

A daily_sales d table contains region, manager_id, and sales_amount. A region may have sales associated with several managers. On a strict SQL database, a developer writes: SELECT d.region, (SELECT m.manager_name FROM managers m WHERE m.manager_id = d.manager_id) AS manager_name, SUM(d.sales_amount) FROM daily_sales d GROUP BY d.region; The report must remain one row per region and does not require a manager name.

Which change most safely corrects the grouping problem while preserving the required result grain?

  1. Add d.manager_id to GROUP BY so the scalar subquery can return a manager for each new group.
  2. Wrap the scalar subquery in MIN so one manager name is selected for every regional group.
  3. Remove the manager-name scalar subquery and select only the region and summed sales amount. (correct answer)
  4. Add DISTINCT to the current selection so duplicate manager names are removed after aggregation.
Explanation: When working with GROUP BY in SQL, a core rule applies: every column in your SELECT list must either appear in the GROUP BY clause or be wrapped in an aggregate function. The query here groups by d.region but includes a scalar subquery referencing d.manager_id — a column not in the GROUP BY. This creates an ambiguity problem: within a single region, multiple managers may exist, so the database doesn't know which manager to return per regional row. The cleanest fix is C — simply remove the scalar subquery and select only d.region and SUM(d.sales_amount). The question explicitly states the report needs one row per region and does not require a manager name. Eliminating the problematic column resolves the grouping violation without adding unnecessary complexity. A is wrong because adding d.manager_id to GROUP BY changes the result grain entirely — you'd get one row per region-manager combination, not one row per region. This breaks the stated requirement. B is wrong for a subtler reason: wrapping the subquery in MIN() would technically suppress the SQL error, but it produces a misleadingly arbitrary manager name (the "minimum" alphabetically). It adds a meaningless column the report doesn't need and masks bad design rather than solving it. D is wrong because DISTINCT operates on full row uniqueness across the entire result set, not on resolving GROUP BY violations. It doesn't fix the underlying aggregation ambiguity at all. Study tip: When you see a GROUP BY query with extra columns in the SELECT, always ask — "Is this column needed?" If the spec doesn't require it, removing it is often the safest and most correct solution.

Question 7

A team_stats table contains multiple rows per team. Each row stores team_id, points_scored, and games_played. The report needs one row per team showing total points divided by total games.

Which selected calculation both complies with grouping rules and computes the requested team-level rate when the query groups only by team_id?

  1. Use SUM(points_scored) / games_played, because only the numerator must be aggregated after grouping.
  2. Use AVG(points_scored / games_played), because averaging row-level rates always equals the team-level rate.
  3. Use SUM(points_scored) / MAX(games_played), because the largest denominator represents the full group.
  4. Use SUM(points_scored) / SUM(games_played), because both row-varying inputs are aggregated for the team. (correct answer)
Explanation: Whenever you see a question about GROUP BY and aggregation, ask yourself two things: does every selected column either appear in the GROUP BY clause or sit inside an aggregate function? And does the aggregation logic actually compute what the problem asks for? Here, the goal is total points for the teamtotal games for the team\frac{\text{total points for the team}}{\text{total games for the team}}, and the query groups by team_id. Since both points_scored and games_played vary across rows, both need to be aggregated before dividing. SUM(points_scored) / SUM(games_played) does exactly that — it pools all points across every row for a team, pools all games, then divides. This gives the true team-level rate, and it satisfies grouping rules because neither raw column appears outside an aggregate. D is the correct answer. A fails on two levels: games_played in the denominator is neither aggregated nor in the GROUP BY clause, which most SQL engines will reject outright. Even if it ran, dividing a summed numerator by a single row's denominator produces a meaningless number. B sounds mathematically appealing, but averaging row-level rates is only equal to the overall rate when every row represents the same number of games — a condition the problem doesn't guarantee. This is a classic weighted-average trap. C uses MAX(games_played) as the denominator, which picks the single largest game count in the group, not the total. Unless games are never split across rows, this understates the true denominator and inflates the rate. A reliable rule of thumb: when building a rate from grouped data, apply the same aggregate function to both the numerator and denominator so you're dividing totals by totals, not mixing levels of aggregation.

Question 8

An orders table contains region, order_date, and order_total. A report must return one row per region and calendar month, with columns for the region, year, month, and total sales. The database applies strict grouping rules.

Which query has the required monthly grain without selecting a non-aggregated value outside the grouping?

  1. Select region, EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date), and SUM(order_total); group by the same region, year expression, and month expression. (correct answer)
  2. Select region, order_date, and SUM(order_total); group by region, the year expression, the month expression, and the complete order date.
  3. Select region, MIN(order_date), and SUM(order_total); group only by region and use the minimum date as the monthly grouping value.
  4. Select DISTINCT region, both date-part expressions, and SUM(order_total); group only by region because distinct removes duplicate months.
Explanation: Whenever you see a SQL grouping question, anchor yourself to one rule: every column in your SELECT list must either appear in the GROUP BY clause or be wrapped in an aggregate function. Databases with strict grouping rules (like standard SQL or PostgreSQL's default mode) will reject any violation outright. Option A gets this exactly right. By selecting region, EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date), and SUM(order_total), and grouping by those same three non-aggregated expressions, every column is accounted for. The year and month extractions carve out the calendar-month grain you need, and SUM aggregates the totals within each group — clean and valid. Option B is a classic trap. Even though the GROUP BY includes the year and month expressions, it also includes the full order_date — and order_date appears in the SELECT without an aggregate. Worse, grouping by the raw date defeats the purpose: you end up with one row per individual date, not per calendar month, destroying the monthly grain entirely. Option C tries to sneak a non-aggregated concept past the engine by wrapping order_date in MIN(). But grouping only by region means every row across all months collapses into one row per region — not one per month. The minimum date doesn't create monthly groupings; it just picks one date from the pile. Option D misunderstands DISTINCT. The keyword removes duplicate result rows, but it does not satisfy GROUP BY requirements. You still cannot reference SUM(order_total) while grouping only by region — the month expressions in SELECT are unaggregated and ungrouped. Study tip: When reviewing a SELECT statement, mentally draw a line between aggregated and non-aggregated columns — every non-aggregated column must appear in GROUP BY, no exceptions.

Question 9

A strict SQL database has a visits table containing first_name, last_name, and visit_id. Assume || is the string-concatenation operator.

Which grouped query is valid because every non-aggregated selected expression is derived entirely from grouped columns?

  1. Select first_name, last_name, and COUNT(*); group only by first_name || ' ' || last_name.
  2. Select first_name, last_name, and COUNT(*); group only by first_name and order by last_name.
  3. Select first_name || ' ' || last_name and COUNT(*); group by both first_name and last_name. (correct answer)
  4. Select first_name || ' ' || last_name and COUNT(*); group only by the first letter of last_name.
Explanation: When working with GROUP BY in strict SQL, the core rule is this: every column that appears in your SELECT clause must either be inside an aggregate function (like COUNT, SUM, AVG) or be functionally determined by the grouped expressions. In other words, SQL needs to know that for each group, there is exactly one possible value for any non-aggregated selected expression. Option C is valid because it selects first_name || ' ' || last_name and groups by both first_name and last_name. Since the concatenated expression is entirely built from those two grouped columns, SQL can unambiguously produce one value per group — the rule is satisfied perfectly. Option A is the trickiest trap. It groups by the concatenation first_name || ' ' || last_name, but then separately selects first_name and last_name as individual columns. The problem is that a single concatenated value like "John Smith" could theoretically come from multiple (first_name, last_name) pairs, so strict SQL cannot guarantee a single value for each raw column. Grouping by the expression doesn't make its parts individually determined. Option B compounds the error from A and also references last_name in an ORDER BY without it being a grouped column — a double violation in strict mode. Option D selects the full concatenation but groups only by the first letter of last_name. That grouping is far too coarse; many different full names could share the same first letter, making the selected expression ambiguous per group. A reliable study tip: ask yourself, "Can I derive my selected expression entirely from the GROUP BY list?" If yes, it's valid. If the SELECT contains anything extra or more specific than what's grouped, it will fail strict SQL validation.

Question 10

A query groups purchase rows by customer_id. The database allows aggregate expressions to appear inside a selected CASE expression but rejects references to ungrouped row-level columns.

Which SELECT list is valid with GROUP BY customer_id?

  1. customer_id, CASE WHEN SUM(amount) > 1000 THEN 'High' ELSE 'Standard' END (correct answer)
  2. customer_id, CASE WHEN amount > 1000 THEN 'High' ELSE 'Standard' END
  3. customer_id, order_date, CASE WHEN SUM(amount) > 1000 THEN 'High' ELSE 'Standard' END
  4. customer_id, COALESCE(payment_status, 'Unknown'), SUM(amount)
Explanation: When working with GROUP BY, the golden rule is that every item in your SELECT list must either be part of the grouping key or wrapped inside an aggregate function. The database collapses many rows into one per group, so referencing a raw, ungrouped column would be ambiguous — which row's value would it show? Option A follows this rule perfectly. customer_id is the grouping key, and SUM(amount) is an aggregate. The CASE expression simply evaluates the result of that aggregate — it never touches a raw column — making the whole construct valid. Option B is the classic trap. The CASE WHEN amount > 1000 references amount directly, which is an ungrouped row-level column. After grouping, there's no single amount value to compare — the database rejects this. Option C adds order_date to the SELECT list without including it in the GROUP BY clause. Even though the CASE expression itself is valid (it uses SUM(amount)), that bare order_date column breaks the rule immediately. Option D includes COALESCE(payment_status, 'Unknown'), which wraps an ungrouped column payment_status in a function — but wrapping a non-aggregated, non-grouped column in a scalar function like COALESCE doesn't make it valid. Only aggregate functions (like SUM, COUNT, MAX) collapse multiple rows into one; COALESCE just handles nulls on a row-by-row basis. Study tip: When evaluating a SELECT list, mentally ask of each column: "Is it in GROUP BY, or inside an aggregate?" If neither, the query will fail — no exceptions for functions like COALESCE or CASE.