Calculate Sums like in Access Totals

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

SQL Server 2005 Forums

Transact-SQL (2005)

Calculate Sums like in Access Totals

Author

Topic

masteripper
Starting Member

25 Posts

Posted - 2008-12-05 : 02:21:01

Hello to everybody
I am wondering if this can happen
I have this query that with the help of CASE produces 2 columns like this
(CASE WHEN (s.WareHouseAA='9') then s.Balance else 0 END) as RawBalance,
(CASE WHEN (s.WareHouseAA='11') then s.Balance else 0 END) as ProductionBalance

Is it possible to have the effect of Sum like Totals in Access
I have a table data like this
Product WarehouseAA Balance
product1 9 9
product1 11 14
product2 9 3
product2 11 12.1
Right now i have

Products Rawbalance ProductionBalance
product1 9 0
product1 0 14
product2 3 0
product2 0 12.1

And i want to have something like this
Products Rawbalance ProductionBalance
product1 9 14
product2 3 12.1
If it has being asked before please don't judge me so hard ... i searched but probably didn't used the corrrent terminology

chrianth
Yak Posting Veteran

50 Posts

Posted - 2008-12-05 : 02:40:58

try this..

select Product, sum(RawBalance) sumRawBalance, sum(ProductionBalance) ProductionBalance
from yourTable
group by Product

hope this helps.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 02:48:18

[code]SELECT Products,
SUM(CASE WHEN (s.WareHouseAA='9') then s.Balance else 0 END) as RawBalance,
SUM(CASE WHEN (s.WareHouseAA='11') then s.Balance else 0 END) as ProductionBalance
FROM Table
GROUP BY Products[/code]

masteripper
Starting Member

25 Posts

Posted - 2008-12-05 : 04:57:12

Well what can i say ur the best.
The Funny thing is that i tried that but i forgot that in group by i have to use Having
(my query is a little more complex)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:10:06

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources