Answered - sum the same field twice in select stmt

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)

Answered - sum the same field twice in select stmt

Author

Topic

sara_23apr
Starting Member

8 Posts

Posted - 2008-03-17 : 20:46:03

Hello friends , I have table (MoneyTrans) with following structure
[Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
I need to write a query to generate following output <br>
Trans Date, total deposits, total withdrawls, closing balance <br>
i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate )
I am working on this for past two days with out getting a right solution. Any help is appreciated
Sara

QAZAFI
Yak Posting Veteran

50 Posts

Posted - 2008-03-17 : 23:17:12

SELECT TransDate,
ISNULL(SUM( CASE WHEN TransName='deposit' then
Amount
end ),0) as DepositSum,
ISNULL(SUM( CASE WHEN TransName='Withdraw' then
Amount
END ),0) as WithdrawSum,
ISNULL(SUM( CASE WHEN TransName='deposit' then
Amount
end ),0)-ISNULL(SUM( CASE WHEN TransName='Withdraw' then
Amount
END ),0) as WithdrawSum

from dbo.DepositWithdraw
group by TransDate

sara_23apr
Starting Member

8 Posts

Posted - 2008-03-18 : 00:52:33

Hi QAZAFI
Thanks for the reply

This is your first post right!!.... so ur first post it self is an answer ! wow! inspiring

Thank you very much

Sara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources