How do you compare values to an average value in SQL?

In recent versions of man database products like SQLServer, MySQL etc you can make use of window functions to calculate the aggregates at the row level and use them to compare to the row values

So in your case to compare to average value you can use a logic like below taking example scenario as query to get details of all employees with salary above average salary for the department

Here t represents a derived table formed out of inner query which uses a window function using PARTITION BY to calculate the average of the salary for the department at each row level and compare it to salary of the employee

1 Like