SQLTeam.com | Weblogs | Forums

Averages


#1

Im using
select
(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.
thankyou


#2

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'

#3

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


#4

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