I have a dataset/table structure like below
|Dept|Rate|No.Of Employee |
|----|----|---------------|
| A | 8 | 2 |
| A | 5 | 2 |
| B | 10 | 2 |
| B | 5 | 2 |
Expecting the output of the SELECT / SQL to be
|Dept|Rate|No.Of Employee | TotalHoursPerWeek | TotalCostPerWeek |TotalCostPerEmplPerDept |
|----|----|---------------|---------------------|--------------------|------------------------|
| A | 8 | 2 | 80 | 640 | 1040 |
| A | 5 | 2 | 80 | 400 | 1040 |
| B | 10 | 2 | 80 | 800 | 1200 |
| B | 5 | 2 | 80 | 400 | 1200 |
I have tried below SELECT
, however not able to SUM 'TotalTotalCostPerWeek' based on 'Dept' & 'Employee'
Please note SUM(TotalCostPerWeek 'per' Dept)
in below query is more for representation purpose, as I know/understand it will not work in SQL, hence need help/suggestion on how to get this kind of result using SELECT
statement.
SELECT Dept, Rate, NoOfEmployee,
(NoOfEmployee * 40) AS TotalHoursPerWeek,
(NoOfEmployee * 40* Rate) AS TotalCostPerWeek,
SUM(TotalCostPerWeek 'per' Dept) AS TotalCostPerEmplPerDept
FROM TABLE
GROUP BY Dept, Rate;
question from:
https://stackoverflow.com/questions/65847056/how-to-sum-of-rows-on-condition-in-next-column-using-select 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…