I have two sql tables provided below:
Worked_Days Table Employee_Info Table
Employee_ID | Dates Employee_ID | Rate | Office
------------------------ ------------------------------
1 | 05/29/2013 1 | 44.95 | AZ
1 | 05/30/2013 2 | 32.63 | AZ
2 | 05/28/2013 3 | 36.52 | VA
3 | 05/30/2013
3 | 05/31/2013
I am looking for a PostgreSQL 9.1 query that will take all the AZ office employees and count each of the days they have worked and multiple them by their respective rates and sum up the multipled values for the whole office.
The expected result for the AZ office is 122.53 = 2 Days * 44.95 + 1 * 32.63
The best that I have been able to do is the following which will count all the days worked for a given office but I am at a loss on how to multiply it by the given employee's rate.
SELECT COUNT(Dates) FROM Worked_Days LEFT JOIN Employee_Info ON Worked_Days.Employee_ID = Employee_Info.Employee_ID WHERE Employee_Info.Office = 'AZ';
Any help would be greatly appreciated. Thank you,
Try this out:
SELECT SUM(ei.Rate) FROM worked_days wd
JOIN employee_info ei ON wd.Employee_ID = ei.Employee_ID
WHERE ei.Office = 'AZ'
This will output the desired number: 122.53
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments