'Find avg of Salary , Departmnent wise , SQL

I would like to find the avg salary from the below table, department wise without a join. The result should have Name, Age, Department, and avg_salary per department

enter image description here

Using a self join as below, is one way I can get the desired output. WOuld like to know option without using a self join.

SELECT name,age, department,avg.avg_salary
FROM Table1
LEFT JOIN 
(SELECT department, avg(salary) as avg_salary
 FROM Table1 GROUP by department) avg
ON avg.department = table1.department
sql


Solution 1:[1]

No platform tagged, but I think most platforms have window functions that you can use like so:

Average Salary Using AVG() Window Function

SELECT name
      ,age
      ,department
      ,AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM Table1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stephan

Related Questions