[Data Analysis]The following SQL code returns an error in the program console: SELECT firstName, lastName, SUM(income) FROM companyRoster SORT BY…

The provided SQL query fails because it attempts to use an aggregate function, SUM(income), alongside non-aggregated columns, firstName and lastName, without specifying how to group the data. According to standard SQL rules, when a SELECT list contains both aggregate functions and regular column names, all non-aggregated columns must be included in a GROUP BY clause. This clause tells the database how to group the rows before applying the aggregate function. Adding GROUP BY firstName, lastName resolves this fundamental error by instructing the database to calculate the sum of income for each unique combination of first and last names.

A. Adding a HAVING clause is used for filtering after aggregation, but it does not resolve the primary error of the missing GROUP BY clause.

C. While ORDER BY is the correct syntax for sorting (not SORT BY), this change alone does not fix the more critical aggregation error.

D. Simply removing the sorting clause does not address the core requirement of a GROUP BY clause when mixing aggregate and non-aggregate columns.

---

1. PostgreSQL 16 Documentation

Section 7.2.4. Aggregate Functions: "In an aggregate query

the SELECT list can only contain aggregate function calls and grouping columns... The reason for this restriction is that otherwise

there would be no way to decide which value of the non-aggregated column to return." This directly explains why the original query is invalid and why a GROUP BY clause is necessary.

2. Stanford University

CS 145 Introduction to Databases

Lecture Notes on "SQL - Aggregation and Grouping": The notes state the following rule: "If SELECT uses aggregation

then any attribute in SELECT list that is not aggregated must be in GROUP BY list." This academic source confirms the fundamental SQL principle violated by the original query.

3. Microsoft SQL Server Documentation

"GROUP BY (Transact-SQL)": "A SELECT statement clause that divides the query result into groups of rows... When a GROUP BY clause is used

each group has a single row that summarizes it

and the SELECT list can only contain... expressions from the GROUP BY clause [and] aggregate functions." This official vendor documentation reinforces the requirement for non-aggregated columns in the SELECT list to be present in the GROUP BY clause.