Two keywords “Group by” and “aggregate function” have been used in this error. So we must understand when and how to use them.
Group by clause:
When an analyst needs to summarize or aggregate the data such as profit, loss, sales, cost, and salary, etc. using SQL, “GROUP BY” is very helpful in this regard. For example, to sum up, daily sales to show to senior management. Similarly, if you want to count the number of students in a department in a university group along with aggregate function will help you attain this.
Group by Split-Apply-Combine strategy:
Group by uses “split-apply-combine” strategy In the figure above we can see that the column has been split into three groups based on first column C1, and then aggregate function is applied on grouped values. At last combine-phase assigns a single value to each group. This can be explained using the example below. First, create a database named “appuals”.
Example:
Create a table “employee” using the following code. Now, Insert data into the table using the following code. The output will be like this. Now select data from the table by executing the following statement. The output will be like this. Now group by the table according to department id.
Error: Column ’employee.sallary’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The error mentioned above arises because the “GROUP BY” query is executed and you have included “employee.salary” column in the select list which is neither part of the group by clause nor included in an aggregate function.
Solution:
As we know that “group by” return single row, so we need to apply an aggregate function to columns not used in group by clause to avoid this error. Finally, apply group by and an aggregate function to find the average salary of the employee in each department by executing the following code. Furthermore, if we depict this table according to split_apply_combine structure it will look like this. The figure above shows that first of all, the table is grouped into three groups according to department id, then aggregate avg() function is applied to find aggregate mean value of salary, which is then combined with department id. Thus the table is grouped by department id and salary is aggregated department wise.
Aggregate functions:
The logical description of the use of group by and aggregate functions together:
Now we will understand the use of “group by” and “aggregate functions” logically via an example. Create a table named “people” in the database by using the following code. Now insert data into the table using the following query. The output will be like: If the analyst needs to know no of residents and their age in the different states. The following query will help him in getting the required results.
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.
On execution of the above-mentioned query, we came across the following error “Msg 8120, Level 16, State 1, Line 16 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 because the “GROUP BY” query is executed and you have included “‘people. age” column in the select list which is neither part of the group by clause nor included in an aggregate function.
Grouping by state arises an error
Logical description and Solution:
This is not a syntax error but it’s a logical error. As we can see that the column “no_of_residents” is returning just a single row, now how can we return the age of all residents in a single column? We can have a list of the age of people separated by commas or the average age, minimum or maximum age. Thus we need more information about the “age” column. We must quantify what we mean by the age column. By age what we want to be returned. Now we can change our question with more specific information about the age column like this. Find no of residents along with the average age of residents in each state. Considering this we have to modify our query as shown below. This will execute without errors and the output will be like this. So it’s also crucial to think logically about what to return in the select statement. Moreover, the following points should be considered in mind while using the “group by” to avoid errors.
Group by and NULL values:
First, add another row into the table named “people” with the “state” column as empty/null. Now execute the following statement. The following figure shows its output. You can see empty value in the state column is considered as a separate group. Now increase no null rows by inserting more rows into the table with null as a state. Now again execute the same query to select output. The result set will be like this. We can see in this figure that an empty column is considered as a separate group and the null column with 2 rows is considered as another separate group with two no of residents. This is how “group by” works.
Fix: The Media Could Not be Loaded, Either Because the Server or Network Failed…Fix: The Specified Domain Either Does Not Exist or Could Not Be ContactedFIX: “(Application Name).exe – Bad Image” is either not designed to run on…Fix: iTunes could not connect to the iphone because an invalid response was…
