This error means that if you’re using GROUP BY to summarize data, any non-aggregated columns must be in the GROUP BY list. If they aren’t, SQL Server will return an error because it doesn’t know which value to show for those fields. This usually happens when you select columns not part of the aggregation in a GROUP BY query. It can also occur due to incorrect grouping or by failing to include all non-aggregated fields in the GROUP BY clause. Two key terms, “Group by” and “aggregate function,” are central to solving SQL errors. Here’s how to use them:
GROUP BY Clause:
The “GROUP BY” clause helps summarize or aggregate data, such as calculating total profits, sales, or counting students in a department. For instance, it can be used to sum daily sales figures.
GROUP BY Split-Apply-Combine Strategy:
“GROUP BY” employs a “split-apply-combine” strategy: In the figure above, column C1 is divided into groups, aggregate functions are applied, and a single value is assigned to each group. Here’s an example using a database named “appuals.”
Example:
Create a table called “employee” using this code: Insert data into the table: Result: Next, view all data: Output: Group by department:
Error: Column ’employee.salary’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
This error occurs because the “employee.salary” column is neither in the GROUP BY clause nor in an aggregate function.
Solution:
Use GROUP BY and an aggregate function to calculate the average salary for each department: Depicted via split-apply-combine:
Aggregate Functions:
Using GROUP BY and Aggregate Functions:
Now, a practical example: Create a table “people“: Insert data: Output: To find the number of residents and their average age by state, use:
Error: Column ‘people.age’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
This error arises when the “people.age” column is not part of the GROUP BY clause nor included in an aggregate function.
Logical Solution:
To fix the query, include the specific aggregation for the “age” column, like the average age: This executes correctly and provides: Consider these points when using GROUP BY to avoid errors:
GROUP BY and NULL Values:
Insert a row with NULL in the “state” column: Now, execute: Output: Add more NULLs: Re-execute the query: In summary, empty or NULL entries are treated as separate groups within the “GROUP BY” operation.
