Databases & SQL Practice Question 4: Group by Ungrouped Column
Databases & SQL · Test 1 · question 4 of 10
A developer wants each customer's total spend and runs this query on PostgreSQL:
SELECT customer_id, order_date, SUM(amount) FROM orders GROUP BY customer_id;
It fails with: column "orders.order_date" must appear in the GROUP BY clause or be used in an aggregate function. What is the cause?
Show answer and explanation
Answer:
After GROUP BY customer_id, each output row stands for a whole group of orders. A customer with five orders has up to five different order_date values, and the database has no rule for choosing one. So standard SQL requires every column in the select list to be either listed in GROUP BY or wrapped in an aggregate. To fix it, drop order_date, aggregate it (for example MAX(order_date) for the latest order), or group by it if you really want per-day totals. PostgreSQL also accepts an ungrouped column that is functionally dependent on a grouped primary key. MySQL enforces the same rule by default through its ONLY_FULL_GROUP_BY mode.
Option A is false. HAVING is optional and only filters groups. SUM ... GROUP BY on its own is the most common aggregate query there is.
Option B is false. GROUP BY columns can be listed in any order and do not have to match the SELECT order.
Option C is false. An alias only names the output column. It is never required for the query to run.
References: PostgreSQL: Table Expressions (GROUP BY and HAVING); MySQL 8.4: MySQL Handling of GROUP BY