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