How to use Aggregate Function without Group By In SQL
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.
Example
Query: Trying to get the average reading with sensor_type
SELECT sensor_type, AVG(reading)
FROM SensorData
Result:
Error in the Query
Explanation: When SQL creates a group which row should be used to show the value with AVG() output thus this error is thrown.
Corrected Query:
SELECT sensor_type, AVG(reading)
FROM SensorData
Group by sensor_type
Go
Output:
Solution of Query 1
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.