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 :-).