I'm trying write a sql statement that will:
show all employee id , name, their wages and the project they are doing if their wage's are greater than average wage.
The wage is calculated by the hourly rate which is located in allocation table and hrsworked which is located in timelog table.
allocation table is a many to many representation of the relationship between employee and project.
The error msg I am getting is:
"A"."PROJID": invalid identifier referring to the A.ProjID in the subquery.
This is what I have done so far:
Can anyone help out as to what I am doing wrong.
SELECT E.EmpID, E.EmpName, P.Description as "Project Description", SUM(A.HourlyRate * T.HrsWorked) AS "Total Pay" FROM EMPLOYEE E INNER JOIN ALLOCATION A ON A.EmpID = E.EmpID INNER JOIN PROJECT P ON A.ProjID = P.ProjID INNER JOIN TIMELOG T ON A.ProjID = T.ProjID AND A.EmpID = T.EmpID GROUP BY E.EmpID, E.EmpName, P.Description HAVING SUM(A.HourlyRate * T.HrsWorked) > (SELECT avg(A.HourlyRate * T.HrsWorked) FROM ATTENDANCE A INNER JOIN TIMELOG T ON A.ProjID = T.ProjID AND A.EmpID = T.EmpID );