Missing a Column in a Group By clause
Sometimes we might be missing the column name from the Group By clause and still, we are using it in the select list and this might also create such an error.
Example
Query: Getting the average reading by sensor_type of each day
SELECT sensor_type, CONVERT(date, reading_time), AVG(reading)
FROM SensorData
Group BY sensor_type
As a result, it will give a similar error which we have seen above regarding the usage of reading_time.
Explanation: The query without reading_time works perfectly as we have seen in the above query. But on adding reading time again the confusion occurs that which date should be added.
Corrected Query:
SELECT sensor_type, CONVERT(date, reading_time), AVG(reading)
FROM SensorData
Group BY sensor_type, CONVERT(date, reading_time)
ORDER BY sensor_type
Output:
These are some common problems that cause such errors. But many times we don’t want to create groups and still want to use aggregate functions and we can do that by using Over().
How to Solve Must Appear in the GROUP BY Clause in SQL Server
In SQL when we work with a table many times we want to use the window functions and sometimes SQL Server throws an error like “Column ‘Employee. Department’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.” This error means that while selecting the columns you are aggregating the functions while some columns are accessed directly which is not possible to show and thus such an error occurs.