I am not getting the logic of how to do Grouping by the Department and Finding the Average of the Salary of the Department and then filtering all the rows of the table by the values that is greater than Average salary of that department in SQL
| Department | Salary |
|---|---|
| A | 100 |
| B | 200 |
| A | 200 |
| B | 50 |
So avg of group A is 150 and avg of grp B is 125 My query should return :-
| Department | Salary |
|---|---|
| B | 200 |
| A | 200 |
CodePudding user response：
You should please have a look how grouping works in SQL. This query will find the department and its average salary:
SELECT department, AVG(salary) salary FROM yourtable
GROUP BY department;
In order to find the departments having a higher salary, you can just join the whole table and this "average result" and choose those entries only that have a higher salary:
SELECT y.department, y.salary FROM yourtable y
JOIN (SELECT department, AVG(salary) salary FROM yourtable
GROUP BY department) average
ON y.department = average.department
WHERE y.salary > average.salary
ORDER BY y.department;
The order clause let department A appear before department B. In your description, it's sorted the other way. If you want to change this, you can write ORDER BY y.department DESC; A last note: If there are NULL values in the salary table, they will note be considered by the average function. So if you have 10 null values, one row with a salary of 100 and one with a salary of 50, the average will be 75 and "ignore" the NULL values. If you don't want this, you need to replace the NULL values by the value you want. As example, you could write COALESCE(salary,0) within your query if you want to replace all NULL values by zero when calculating your average salary.