CompTIA Tech+ (FC0-U71)Data and Database FundamentalsHard
A manager wants a single SQL query that returns the total number of orders placed by each customer from an Orders table, grouped by CustomerID. Which SQL clause combination is required to achieve this?
- ASELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID;
- BSELECT COUNT(*) FROM Orders WHERE CustomerID IS NOT NULL;
- CSELECT CustomerID, COUNT(*) FROM Orders ORDER BY CustomerID;
- DSELECT CustomerID FROM Orders WHERE COUNT(*) > 0;
Show answer & explanationAnswer & explanation
Correct answer: A. SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID;
GROUP BY combined with the COUNT(*) aggregate function groups rows by CustomerID and counts how many orders fall into each group, producing a per-customer total. ORDER BY only sorts results without grouping, WHERE cannot filter on an aggregate function, and the last option only counts total rows without grouping by customer.
Why the other options are wrong
- B. This returns one overall count of all orders, not per-customer totals.
- C. ORDER BY only sorts the output; it does not group or aggregate rows.
- D. WHERE cannot use an aggregate function like COUNT(*) directly in this way.
SQL Aggregate Function with GROUP BY
Aggregate functions like COUNT(), SUM(), and AVG() calculate a single value from a group of rows, and GROUP BY organizes rows into those groups based on a shared column value.
- COUNT(*) counts the number of rows in each group
- GROUP BY must include the non-aggregated columns being selected
- Common aggregates: COUNT, SUM, AVG, MIN, MAX
Memory trick: GROUP BY sorts customers into buckets, COUNT tallies each bucket.