sum column 1 and sum column 2 in one statement

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)

sum column 1 and sum column 2 in one statement

Author

Topic

lemx67
Starting Member

6 Posts

Posted - 2012-11-21 : 01:10:43

Hi there, I hope you can help me with this problem.

I have a table with columns (currency, book, amount), with sample values
(USD, F, 100.00),
(USD, L, 60.00),
(USD, F, 25.00),
(AUD, F, 45.00),
(GBP, L, 80.00),
(GBP, L, 40.00),
(EUR, L, 27.00),
(EUR, L, 13.00),
(EUR, F, 30.00).

I need a result set that reads

Currency Foreign(F) Local(L) Total
USD 125.00 60.00 185.00
AUD 45.00 0.00 45.00
GBP 0.00 120.00 120.00
EUR 30.00 40.00 70.00

Can you help me with the SQL statement. I have a problem with the SUM since there are 2 columns you have to sum on.

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-21 : 02:35:57

[code]-- making some testdata
declare @sample table(currency varchar(10), book char(1), amount decimal(12,2))
insert @sample
select 'USD', 'F', 100.00 union all
select 'USD', 'L', 60.00 union all
select 'USD', 'F', 25.00 union all
select 'AUD', 'F', 45.00 union all
select 'GBP', 'L', 80.00 union all
select 'GBP', 'L', 40.00 union all
select 'EUR', 'L', 27.00 union all
select 'EUR', 'L', 13.00 union all
select 'EUR', 'F', 30.00

-- the solution
select
currency,
sum(case when book='F' then amount else 0 end) as [foreign(f)],
sum(case when book='L' then amount else 0 end) as [local(l)],
sum(amount) as total
from @sample
group by currency
[/code]

Too old to Rock'n'Roll too young to die.

lemx67
Starting Member

6 Posts

Posted - 2012-11-21 : 02:44:21

Thank you so much. It seems so logical yet all I could come up with was

SELECT currency,
(SELECT SUM(B.amount) FROM Accts B WHERE B.currency = A.currency
AND book = 'F'),
(SELECT SUM(C.amount) FROM Accts C WHERE C.currency = A.currency
AND book = 'L')
FROM Accts A
GROUP BY currency

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-21 : 02:48:29

You are welcome

quote:
Originally posted by lemx67

Thanks you so much. It seems so logical yet all I could come up with was

SELECT currency,
(SELECT SUM(B.amount) FROM Accts B WHERE B.currency = A.currency
AND book = 'F'),
(SELECT SUM(C.amount) FROM Accts C WHERE C.currency = A.currency
AND book = 'F') should be 'L' here...
FROM Accts A
GROUP BY currency

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources