5 Replies Latest reply on Oct 9, 2012 7:01 PM by carlo.m

    weighted average executesql

    carlo.m

      I'm trying to get a weighted average using executesql and hitting a wall.

       

      The query looks like this

       

      Select Account, sum(case when Week=30 then sales else 0 end) / sum(case when week=30 then people else 0 end)

      from sales

      group by Account

       

      If I change the "/" for a comma it returns each column with no problem. but when I try to divide the total of sales for a given week and the total of people for a given week to get the sales per person, I get nothing...

       

      NOTE: I posted an abreviated version of the query, the query has several CASE functions for several weeks but the number of case statements isn't the issue at hand.

       

      Any ideas? Work arounds?