February 15th, 2013, 03:07 PM
Join Date: Feb 2013
Time spent in forums: 20 m 47 sec
Reputation Power: 0
Referencing a derived value in an INNER JOIN?
I wonder if anyone can please advise me on how I might restructure this query so that I can use one of the derived values (average_score, as listed in line 3) further down inside the second INNER_JOIN statement?
I suspect that it doesn't work because the INNER_JOIN parts gets calculated before the derived values get created and therefore they are NULL?
SELECT e.index, e.name, e.occupation, e.location,
COUNT(r.index) AS number_of_reports,
ROUND(AVG(r.score)) AS average_score,
ROUND(SUM(r.cost)/SUM(r.hours_worked)*60) AS hourly_rate,
g.grade as grade
FROM `employees` AS e
INNER JOIN `reports` AS r
ON e.index = r.host_index
INNER JOIN `grades` as g
GROUP BY e.index, e.name, e.location
HAVING COUNT(r.index) >= 2
ORDER BY AVG(r.score) DESC
Thanks for any help!