| Author |
 Topic  |
|
|
lemx67
Starting Member
6 Posts |
 Posted - 11/21/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 11/21/2012 : 02:35:57
|
-- 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
Too old to Rock'n'Roll too young to die. |
 |
|
|
lemx67
Starting Member
6 Posts |
Posted - 11/21/2012 : 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 |
Edited by - lemx67 on 11/21/2012 03:05:00 |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 11/21/2012 : 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. |
|
|
| |
Topic |
|
|
|
sum column 1 and sum column 2 in one statement
