How to use OVER() to eliminate error without using Group By In SQL
We will take the first query in which we got the error. In that, we were taking the sensor_type with the average of reading_time. To use OVER() we have to give this clause after the aggregate function. In general OVER clause is used with window functions to perform calculations across a set of rows related to the current row.
Query:
SELECT sensor_type, AVG(reading) OVER()
FROM SensorData
Output:
Explanation: This query has not given any error to us. But you can see that the output in the average column is the same in all the rows. Because it took the partition as the whole table and thus it has made the average of all. And thus whenever you are using OVER try to use it with condition.
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.