1 of 1 people found this helpful
Can't you just use the version of the SQL with the comma to get the two sums, then Evaluate ( Substitute ( ~sqlResult ; "," ; "/" ) ) in a non-SQL calculation?
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.
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:
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...
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 "?".
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??