AnsweredAssumed Answered

weighted average executesql

Question asked by carlo.m on Oct 1, 2012
Latest reply on Oct 9, 2012 by 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?

Outcomes