Q: MYSQL: Get average of fields in case where value is not a certain value Say I have a table with 5 columns,
A, B, C, D, E
that are integers. I want to get the average of all of the fields in that case, that are not 3.
So, on some sample data:
A, B, C, D, E         DESIRED RESULT
-------------
1, 1, 4, 4, 3       -> 2.5 (NOT 2.6)
1, 2, 3, 3, 3       -> 1.5 (NOT 2.4)

EDIT: I found a solution.
(
   (
    IF(A!=3,A,0)
   +IF(B!=3,B,0)
   +IF(C!=3,C,0)
   +IF(D!=3,D,0) 
   +IF(E!=3,E,0)
   ) 
   /
   (
    IF(A!=3,1,0)
   +IF(B!=3,1,0)
   +IF(C!=3,1,0)
   +IF(D!=3,1,0)
   +IF(E!=3,1,0)
   )
) as VALUE


A: Try that:
  select avg(CASE WHEN A = 3 then 0 else A end +
             CASE WHEN B = 3 then 0 else B end +
             CASE WHEN C = 3 then 0 else C end +
             CASE WHEN D = 3 then 0 else D end +
             CASE WHEN E = 3 then 0 else E end)/
         (CASE WHEN A = 3  then 0 else 1 end +
          CASE WHEN B = 3  then 0 else 1 end +
          CASE WHEN C = 3  then 0 else 1 end +
          CASE WHEN D = 3  then 0 else 1 end +
          CASE WHEN E = 3  then 0 else 1 end) as av from table1
  group by A,B,C,D,E

DEMO HERE
