How to use OVER() to eliminate error without using Group By In SQL

Fixing the “must appear in the GROUP BY clause or be used in an aggregate function” Error

The most common way to fix this is to use GROUP BY in the SELECT statement. This statement only applies to the SELECT statement, not to the aggregate statement....

1. Using Aggregate Function without Group By

Many times we use aggregate functions like Sum, Count, Max, and Min to perform calculations on the groups. These functions collapse multiple rows into a single result based on the specified grouping criteria. With these values when we try to show or select any other column that is not surrounded by an aggregate function such error occurs....

2. 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....

Using OVER() to eliminate error without using Group By

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....

Conclusion

So, when this error occurs try to check the list in the select statement and match it with the list given in group by clause. Missing the column in any one of the list will generate this error. Thus always try to think of what to include in the Select list if you are using the Group by Clause. By following these solutions, you can create well-formed queries that meet the requirements of SQL Server’s grouping and aggregation rules....