-2

I'm getting the #1111 invalid use of Group function for the following query - but frankly I have been over it time and again with nothing striking me as incorrect. is there something I'm missing?

Had the query in sublime checking the obscene number of brackets is correct and it marries up too...

SELECT CURRENT_TIME,
s.`kickoff`,
s.`home`,s.`away`,
Sum(((i.010H+i.010A+i.1020H+i.1020A+i.2030H+i.2030A+i.3040H+i.3040A+i.4045H+i.4045A)/(count(i.home)))*100) as averageFHGHome,
sum(((case when i.010H+i.010A+i.1020H+i.1020A+i.2030H+i.2030A+i.3040H+i.3040A+i.4045H+i.4045A>0.5 then 1 else 0 end)/(count(i.home)))*100) as occurFHGHome,
sum(((case when i.010H+i.010A+i.1020H+i.1020A+i.2030H+i.2030A+i.3040H+i.3040A+i.4045H+i.4045A>1.5 then 1 else 0 end)/(count(i.home)))*100) as occur2FHGHome,
Sum(((i.4560H+i.6070H+i.7080H+i.80FTH+i.4560A+i.6070A+i.7080A+i.80FTA)/(count(i.home)))*100) as averageSHGHome,
sum(((case when i.4560H+i.6070H+i.7080H+i.80FTH+i.4560A+i.6070A+i.7080A+i.80FTA>0.5 then 1 else 0 end)/(count(i.home)))*100) as occurSHGHome,
sum(((case when i.4560H+i.6070H+i.7080H+i.80FTH+i.4560A+i.6070A+i.7080A+i.80FTA>1.5 then 1 else 0 end)/(count(i.home)))*100) as occur2SHGHome,
Sum(((i.010H+i.010A+i.1020H+i.1020A+i.2030H+i.2030A+i.3040H+i.3040A+i.4045H+i.4045A+i.4560H+i.6070H+i.7080H+i.80FTH+i.4560A+i.6070A+i.7080A+i.80FTA)/(count(i.home)))*100) as averageMG,
sum(((case when i.010H+i.010A+i.1020H+i.1020A+i.2030H+i.2030A+i.3040H+i.3040A+i.4045H+i.4045A+i.4560H+i.6070H+i.7080H+i.80FTH+i.4560A+i.6070A+i.7080A+i.80FTA>0.5 then 1 else 0 end)/(count(i.home)))*100) as occur1MG,
sum(((case when i.010H+i.010A+i.1020H+i.1020A+i.2030H+i.2030A+i.3040H+i.3040A+i.4045H+i.4045A+i.4560H+i.6070H+i.7080H+i.80FTH+i.4560A+i.6070A+i.7080A+i.80FTA>1.5 then 1 else 0 end)/(count(i.home)))*100) as occur2MG,
sum(((case when i.010H+i.010A+i.1020H+i.1020A+i.2030H+i.2030A+i.3040H+i.3040A+i.4045H+i.4045A+i.4560H+i.6070H+i.7080H+i.80FTH+i.4560A+i.6070A+i.7080A+i.80FTA>2.5 then 1 else 0 end)/(count(i.home)))*100) as occur3MG
from saturday s
inner join ITACornerNew i on s.home=i.Home
group by s.home
HAVING s.kickoff > subTIME(CURRENT_TIME, '01:45:00') and s.kickoff < CURRENT_TIME
order by s.kickoff ASC
C-Sway
  • 357
  • 1
  • 2
  • 13

1 Answers1

1

As a result of your "obscene number of brackets", you ended up with the invalid

Sum(...count(...)...)

which would be the aggregation of an aggregation, while you are probably looking for something like 

Sum(...)/count(...)

Not directly related to your current problem, but additionally, if s.home is not your primary key, you might either get incorrect results or an error message, since s.kickoff and s.away are not part of your group by. And, as @Strawberry already mentioned, you should strongly consider a redesign of ITACornerNew.

Solarflare
  • 10,721
  • 2
  • 18
  • 35