How I can get the best optimized answer for below?
Input:
| Emp. | Dept | Emp_Salary |
|---|---|---|
| 1 | a1 | 100 |
| 2 | a1 | 200 |
| 3 | a1 | 300 |
| 4 | a2 | 200 |
| 5 | a2 | 100 |
Output:
| Emp. | Dept | Emp_Salary | Dept_Total_Salary |
|---|---|---|---|
| 1 | a1 | 100 | 600 |
| 2 | a1 | 200 | 600 |
| 3 | a1 | 300 | 600 |
| 4 | a2 | 200 | 300 |
| 5 | a2 | 100 | 300 |
How can I create this with best optimized way with 1 table scan only...I am able to do it with Subquery but would like how
CodePudding user response：
You may use SUM() as an analytic function here:
SELECT Emp, Dept, Emp_Salary,
SUM(Emp_Salary) OVER (PARTITION BY Dept) AS Dept_Total_Salary
FROM Employee
ORDER BY Emp;
CodePudding user response：
use this
select Emp,Dept,Emp_Salary,
sum(Emp_Salary) over (PARTITION BY Dept ORDER BY Dept) as Dept_Total_Salary
from Employee
CodePudding user response：
SELECT e.*,
SUM(emp_salary) OVER(PARTITION BY dept) dept_toatl_salary
FROM employee e
ORDER BY emp;