summing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

summing

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-05-25 : 10:43:21

So below, I have multiplied all the amounts by -1.
I the want to sum teh total.
It isnt quite working. Thanks in advance for your help on this.

select budgetyear,LEID,BusinessUnitID,OBJ,SUB,ICTypeKey,
-1*Total as total,-1*Jan,-1*Feb,-1*Mar,-1*Apr,-1*May,-1*Jun,-1*Jul,-1*Aug,-1*Sep,-1*Oct,-1*Nov,-1*Dec
from Upload_GeneralExpense
where LEID = 31001
compute sum(total)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-25 : 10:49:08

You mean you want the sum of Jan + Feb + ...+ Dec?

Jim

Everyday I learn something that somebody else already knew

tariq2
Posting Yak Master

125 Posts

Posted - 2011-05-25 : 10:55:08

Yes, effectively...
But I want to be able to sum just the TOTAL column

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-25 : 11:22:09

I'm having truoble understanding what you mean; others here are good at guessing , but I'm not. Could you provide some sample data and what your expected output would be?

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 11:34:38

I am in Jim's camp on ability to guess, but lack of skill has never stopped me from offering my opinion, so here it is:

Change the query to sum the exact thing you have in the select list as in:


select budgetyear,LEID,BusinessUnitID,OBJ,SUB,ICTypeKey,
-1*Total as total,-1*Jan,-1*Feb,-1*Mar,-1*Apr,-1*May,-1*Jun,-1*Jul,-1*Aug,-1*Sep,-1*Oct,-1*Nov,-1*Dec
from Upload_GeneralExpense
where LEID = 31001
compute sum(-1*Total)

Also, according to Microsoft, http://msdn.microsoft.com/en-us/library/ms181708.aspx they want to remove this feature in the next version of SQL server. So they recommend that you use ROLLUP.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources