SQLTeam.com | Weblogs | Forums



Im using
(SELECT (AVG(FTHG)) from [2013-2014] WHERE div='E0' AND [date]<('2014-05-13') AND HomeTeam='Arsenal')
(select avg(FTHG) from [2013-2014] WHERE div='E0') to get first the average home goals for aresenal and deviding by the average home goals for the league. This works but is there a tidier way of doing this.


I think so, I think this will do it with only a single pass of the table:

select avg(FTHG) / AVG(case when [date]<('20140513') AND HomeTeam='Arsenal' 
    Then FTHG End) AS final_result
from [2013-2014] 
WHERE div='E0'


Thank you for your reply. Looks so much cleaner, I swapped the div around and it does exactly what i need. Than you


Great. It also does only one scan of the table instead of two :-).