
1. Re: weighted average executesql
jbante Oct 2, 2012 7:28 AM (in response to carlo.m)1 of 1 people found this helpfulCan't you just use the version of the SQL with the comma to get the two sums, then Evaluate ( Substitute ( ~sqlResult ; "," ; "/" ) ) in a nonSQL calculation?

2. Re: weighted average executesql
beverly Oct 2, 2012 8:44 AM (in response to jbante)I wondered the same think J, but thought there might be more than on result line. And the the first field if also in the query:
Account, sum(case when Week=30 then sales else 0 end) / sum(case when week=30 then people else 0 end)
but I suppose something that 'loops' the results could be done.
Beverly

3. Re: weighted average executesql
carlo.m Oct 2, 2012 9:19 AM (in response to jbante)Good idea as a workaround.
However Beverly is correct. There will be scenarios in which there will be more than one result line and more than one column as well. A query could return something like this:
Account,Part,SumSales,SumPeople
A,X,1000,100
B,Y,1500,9
C,Z,2000,40
Looping through this can with your idea can produce the end result, but I'm looking to find a simpler solution... hopefully there is one to be found...

4. Re: weighted average executesql
greglane Oct 2, 2012 10:25 AM (in response to carlo.m)The problem is likely that one or more of your groups is dividing by zero. If any record tries to divide by zero, ExecuteSQL will return "?".

5. Re: weighted average executesql
carlo.m Oct 9, 2012 7:01 PM (in response to greglane)so you are saying that it is possible to do operations on aggregates?
How can I get around dividing by zero. I don't believe Coalesce is supported??