DA0-002 · Question #61
The following SQL code returns an error in the program console: SELECT firstName, lastName, SUM(income) FROM companyRoster SORT BY lastName, income Which of the following changes allows this SQL…
The correct answer is B. SELECT firstName, lastName, SUM(income) FROM companyRoster GROUP BY firstName. The original SQL query fails because it uses an aggregate function (SUM) with non-aggregated columns (firstName, lastName) without a GROUP BY clause. To resolve this, all non-aggregated columns in the SELECT statement must be included in a GROUP BY clause.
Question
The following SQL code returns an error in the program console:
SELECT firstName, lastName, SUM(income) FROM companyRoster SORT BY lastName, income Which of the following changes allows this SQL code to run?
Options
- ASELECT firstName, lastName, SUM(income) FROM companyRoster HAVING SUM(income) >
- BSELECT firstName, lastName, SUM(income) FROM companyRoster GROUP BY firstName,
- CSELECT firstName, lastName, SUM(income) FROM companyRoster ORDER BY firstName,
- DSELECT firstName, lastName, SUM(income) FROM companyRoster
How the community answered
(32 responses)- A16% (5)
- B75% (24)
- C3% (1)
- D6% (2)
Why each option
The original SQL query fails because it uses an aggregate function (SUM) with non-aggregated columns (firstName, lastName) without a GROUP BY clause. To resolve this, all non-aggregated columns in the SELECT statement must be included in a GROUP BY clause.
The `HAVING` clause filters groups based on aggregate conditions and requires a `GROUP BY` clause to precede it, but it does not resolve the initial error of combining aggregate and non-aggregate columns without a `GROUP BY`.
Adding `GROUP BY firstName, lastName` allows the SQL query to correctly execute. When an aggregate function like `SUM()` is used in the `SELECT` clause, any non-aggregated columns (like `firstName` and `lastName`) must be listed in the `GROUP BY` clause to define the groups over which the aggregate function operates. This ensures that the sum of income is calculated for each unique combination of first and last names.
`ORDER BY` is used for sorting the final result set and does not address the requirement to group non-aggregated columns when an aggregate function is present in the `SELECT` clause.
This choice is essentially the original problematic query, missing the necessary `GROUP BY` clause to combine aggregate and non-aggregate columns.
Concept tested: SQL GROUP BY clause with aggregate functions
Source: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql
Topics
Community Discussion
No community discussion yet for this question.