save sql SUM() into a variable

I'm just wondering if we can save sql aggregate functions into a variable.

I have this query:

select company, dept, sum(pers) as pers1, sum(amount) as amount1,
sum(amount)/sum(pers) as wage
from xxtestsum
group by company, dept

instead of calling sum(amount) and sum(pers) again in "sum(amount)/sum(pers) as wage",
I would like to save them in some kinda variable in the select clause so it will save process time.

Sorry but im new to sql programming. So thx for your understanding.

If you are worried about a performance loss in that sql sentence because of the repeating sum, you shoudn't be. Sql Server's query optimized will automatically cache the results of each aggregate function, so the second call to sum(amount) will actually not force the sum aggregate to run again. That is, provided both sum aggregates are being affected by the very same group by clause.

Anyway, if you still want to get those values into a variable and you are using an sqldatasource (this forum is for that...), then you should know that you can call the "Select" method on the datasource manually. This will return you an IEnumerable object which, depending on how the datasource is configured, will either be a DataView or a DataReader. Using any of those, you can extract the full result of your query and do with it as you wish.

|||

Thanks for the clarification

Sanity Check: Measure Expression nearly always better than Calculated Measure?

Hey All --

From what I've read, Measure Expressions are generally superior to Calculated Measures because we can actually aggregate them. Most of the examples of Measure Expressions I've seen are something along the lines of [Measures].[Sales] / [Measures].[Sales Cost]...Both values are part of various measure groups.

I assume that even if I 'hard code" a value as in the following example my performance is STILL going to be much better in a measure expression (assuming we take advantage of aggs) than doing the same thing in a calculated measure:

[Measures].[Sales SubTotal] * 1.08 //Sales Tax)

So, I guess the question I'm asking is: Must a "valid" measure expression include ONLY measures and operators, or can it include any hard coded constant value I want to stick in, as well...And if the latter situation is "OK", we still get all of our perf benefits, etc.

Thanks!

The best thing to do would be to give it a shot with both approaches and see which works best for you. Look at both query response time and processing time impacts.

The SSAS 2005 Performance Guide suggests this is an optimal solution when working with data from multiple measure groups ... which is the case you identify above. Again, try it and see.

The easiest way to compare the two would be to go ahead and create the Measure Expression, process your cube, execute an MDX query to return data from the measure with the defined measure expression. Then, recreate the calculation in a WITH MEMBER clause of another MDX statement and compare its execution time to the original.

Good Luck,
Bryan