importing data from several sheets and average without including 0s

Question

I have the following formula: =query({'6th LA'!A2:G66;'6th SS'!A2:G66;'6th Math'!A2:G66;'6th Sci'!A2:G66;'7th LA'!A2:G93;'7th Sci'!A2:G93;'7th SS'!A2:G93;'7th Math'!A2:G93;'8th LA'!A2:G80;'8th SS'!A2:G80;'8th Math'!A2:G80;'8th Sci'!A2:G80;'8th Keyboarding'!A2:G80;PE!A2:G237;Drama!A2:G237;Art!A2:G237;Music!A2:G237;'Monica Robison'!A2:G237},"select Col1, Avg(Col7) where Col1 != '' group by Col1 label AVG(Col7) 'Overall Concern'")

It works great, but when it averages column 7 across all the sheets according to the student name, it is dividing by all the sheets the student is in and not taking out the Null values. So for example, I have 2,2,3,3,5,0,0,4. The average should be 3.1 if 0s are not counted, but it is coming back as 2 because it is including the 0s.

How do I fix this?

Answer 1

Add another condition with and, like this:

=query( 
  {...}, 
  "select Col1, avg(Col7) 
   where Col1 is not null 
   and Col7 <> 0 
   group by Col1 
   label avg(Col7) 'Overall Concern' ", 
  0 
)