Question 1

Tied aggregates and sort order

Accepted Answer

East totals 500 + 200 = 700 and West totals 300 + 400 = 700. Both groups have the same total, so ORDER BY total DESC produces a tie - the database may return them in any order. In practice most engines return East before West here (alphabetical tiebreak on region), giving East | 700 then West | 700. The alias 'total' is usable in ORDER BY because ORDER BY is evaluated after SELECT.

Question 2

What does grouping rows actually produce?

Accepted Answer

GROUP BY customer collapses all rows with the same customer into one group. Alice has two orders (100 + 300 = 400); Bob has one (200). SUM operates within each group independently. The result has one row per unique customer value.

Question 3

How many rows survive after grouping?

Accepted Answer

COUNT(*) counts all rows in each group. Alice appears in rows 1, 3, and 4 - so her count is 3. Bob appears only in row 2 - count 1. GROUP BY determines the groups; the aggregate function computes the metric for each.

Question 4

Composite key grouping - how many groups?

Accepted Answer

GROUP BY customer, status creates one group per unique combination of customer and status. The distinct pairs are: (Alice, shipped) - 2 rows, (Alice, pending) - 1 row, (Bob, pending) - 1 row. That gives 3 result rows. Think of multi-column GROUP BY as defining a composite key for each group.

Question 5

Same product, different region - same group?

Accepted Answer

The unique (region, product) pairs are: (East, Widget) with 2 rows, (East, Gadget) with 1 row, and (West, Widget) with 1 row. Each distinct combination becomes one output row, so the result has 3 rows. Notice that (East, Widget) and (West, Widget) are different groups even though they share the same product - the region differs.

Question 6

Using an alias in ORDER BY after grouping

Accepted Answer

GROUP BY customer first collapses rows: Alice = 100 + 200 = 300, Bob = 400, Carol = 150. ORDER BY total DESC then ranks the groups: 400 first, 300 second, 150 last. You can sort by an aggregate alias in ORDER BY because ORDER BY runs after both GROUP BY and SELECT.

Question 7

What does GROUP BY do?

Accepted Answer

GROUP BY partitions the rows into buckets based on the specified column(s), then aggregate functions like SUM, COUNT, and AVG run once per bucket. Without GROUP BY, an aggregate function treats the entire table as one group. Every column in the SELECT list must either appear in GROUP BY or be wrapped in an aggregate - mixing is what causes the 'non-aggregate column' error.

Question 8

One number per department - which math?

Accepted Answer

AVG computes the mean within each group. Eng: (90000 + 80000) / 2 = 85000. Sales: (60000 + 70000) / 2 = 65000. ORDER BY dept puts Eng before Sales alphabetically.

Question 9

Non-grouped column in SELECT

Accepted Answer

Every column in SELECT must either appear in GROUP BY or be wrapped in an aggregate (SUM, MAX, etc.). When a dept has multiple employees, there's no single name that represents the group. Fix options: add name to GROUP BY (one row per dept+name combo), use MAX(name) to pick one name per dept, or remove name from SELECT.

Question 10

WHERE meets GROUP BY - order of operations

Accepted Answer

WHERE runs before GROUP BY. Only shipped orders survive the filter - Alice's 100 and Bob's 300. Then GROUP BY customer groups the two remaining rows, giving one sum per customer. If you need to filter on aggregate results (e.g. sum > 200), you need HAVING, not WHERE.

Question 11

Which SELECT is valid with GROUP BY dept?

Accepted Answer

With GROUP BY dept, only dept can appear outside an aggregate in SELECT. COUNT(*) and MAX(salary) are aggregate functions, so they are valid alongside dept. The other options include name or id without wrapping them in aggregates - both would be ambiguous because multiple names or ids can exist per dept group.

Question 12

Filtering on a total - which groups survive?

Accepted Answer

HAVING filters groups after aggregation, unlike WHERE which filters rows before grouping. The groups are: Alice = 300, Bob = 400, Carol = 50. HAVING SUM(amount) > 200 keeps Alice (300) and Bob (400) and drops Carol (50). Use WHERE to filter individual rows before grouping; use HAVING to filter the resulting groups.

Question 13

GROUP BY vs DISTINCT

Accepted Answer

For selecting unique values of a single column, DISTINCT and GROUP BY are interchangeable in result. The practical difference: GROUP BY lets you add aggregate columns (COUNT, SUM) alongside the grouped column; DISTINCT does not. Performance varies by database and indexes - neither is universally faster. Both handle NULLs the same way (one NULL group).

Grouping