Question 1

Basic GROUP BY

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 2

COUNT per group

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 3

GROUP BY with ORDER BY

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 4

GROUP BY multiple columns

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

Distinct groups with multi-column GROUP BY

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

Sort grouped results by aggregate value

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.