mysql - How to SUM of rows on Condition in next column using SELECT

Question

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

Answer 1

I think I understand what you need and you can use "case when" to achieve this...

Select Dept,
       Rate,
       No_of_employee,
       TotalHoursPerWeek = (No_of_employee * 40),
       TotalCostPerWeek = (No_of_employee * 40 * Rate)
       TotalCostPerEmplPerDep = case when Dept ='A' then (select SUM(No_of_employee * 40 * Rate) from table where Dept = 'A')
                                 else (select SUM(No_of_employee * 40 * Rate) from table where Dept <> 'A')    
from table