Get the sum per row based on the user

Question

Just want to ask for help on how to get the sum per row based on the user? I'm trying to get the cost of the project by multiplying the hours worked of the employee and then compute it to the employee's rate. I have 3 tables: Employee, Hours, Projects

This is similar to what my table looks like

For the Employee Table:

Name    |   Rate    |
Emp_A   |   10.50   |
Emp_B   |   15.00   |
Emp_C   |   25.10   |

For the Hours Table:

Employee    |   Hours   | Project
Emp_A       |   18.50   | PRJ.APP
Emp_A       |   10.00   | PRJ.APP
Emp_B       |   20.00   | PRJ.APP
Emp_B       |   5.30    | PRJ.APP
Emp_C       |   13.00   | PRJ.CAT
Emp_C       |   8.00    | PRJ.CAT

For the Projects Table:

Prj_Name    |   Prj_Code    |
Apple       |   PRJ.APP     |
Catalina    |   PRJ.CAT     |

This is the code I currently have, its just to get the sum based on the project and user

SELECT users.employee, SUM(timesheet.hours * 10.50)

FROM Projects prj, Hours timesheet, Employee users

WHERE prj.Prj_Name = 'PRJ.APP'

The output I was hoping to get is for example when the project name PRJ.APP is selected, it gets the employee that has worked on that project and will compute it per row since the rate is different per employee.

I tried doing it something like

SELECT if(users.employee = 'Emp_A', SUM(timesheet.hours * 10.50), SUM(timesheet.hours))

FROM Projects prj, Hours timesheet, Employee users

WHERE prj.Prj_Name = 'PRJ.APP'

but this will only get the sum of Emp_A and based on the example table Emp_A and Emp_B worked on project PRJ.APP.

Desired output for the project PRJ.APP (employee A and B worked on the PRJ.APP):

|   Emp     |   Hours   |   Hours_Sum   |
|   A       |   28.50   |   285         |
|   B       |   25.30   |   379.5       |

The hours column is the sum of "Hours" from the Hours table And the Hours_Sum is the SUM of Hours column multiplied by the employees respective rates..

I hope you guys can help me. Thank you in advance!

Answer 1

modify your query as shown, you have to use both join and group by clause in your query. This query will return the result as the total rate based on the hrs spent for each proejct of each employee

select e.name, p.prj_name,SUM(h.hours * e.rate)
from employee e inner join hours h on (e.name = h.employee)
inner join projects p on (p.prj_code = h.project)
group by e.name, p.prj_name