7 Replies Latest reply on Mar 20, 2013 3:12 PM by Ashley Howard

    Aggregations in Case Statement

    gareth agius

      Hi,

       

      I am trying to create a calculated value with the case statement shown below:

       

      CASE [store_group]

      WHEN 'H' THEN AVG(IF [store_group] = 'H' THEN [sales] ELSE 0  END )

      WHEN 'F' THEN  AVG(IF [store_group] = 'F' THEN [sales] ELSE 0 END )

      WHEN 'S' THEN  AVG(IF [store_group] = 'S' THEN [sales] ELSE 0 END )

      WHEN 'M' THEN  AVG(IF [store_group] = 'M' THEN [sales] ELSE 0 END )

      END

       

      So the above should check every row, and wherever the [store_group] equals 'H' it returns the average value for all the "sales" values of [store_group] = H.

       

      However i am getting an error "Cannot mix aggregate and non-aggregate comparisons or results in case", i dont see any mixing of aggregations in the above.

       

      Could any one shed any light please?

       

      Thanks in advance

       

      Gareth