select query - 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

select query

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-02-27 : 06:34:57

Hi ,
I need in one stroke country_name,currency_code,base_amount though currency code is differing but same country
table..can any one help?

table structure
table name:testing

country_name,curr_code,base_amount
-------------------------------
swiss chf 1000
swiss eur 2000

Actual output should be
swiss chf 3000

as per my logic the o/p is coming as:
swiss CHF 2000
swiss CHF 1000

can any one correct the below
logic query is:
select country_name,case when country_name='swiss' and curr_code in('EUR','CHF') then 'CHF' ELSE curr_code end as curr,
sum(base_amt)
from testing
group by country_name,curr_code

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-27 : 06:40:34

quote:
Originally posted by sent_sara

Hi ,
I need in one stroke country_name,currency_code,base_amount though currency code is differing but same country
table..can any one help?

table structure
table name:testing

country_name,curr_code,base_amount
-------------------------------
swiss chf 1000
swiss eur 2000

Actual output should be
swiss chf 3000

as per my logic the o/p is coming as:
swiss CHF 2000
swiss CHF 1000

can any one correct the below
logic query is:
select country_name,case when country_name='swiss' and curr_code in('EUR','CHF') then 'CHF' ELSE curr_code end as curr,
sum(base_amt)
from testing
group by country_name,case when country_name='swiss' and curr_code in('EUR','CHF') then 'CHF' ELSE curr_code end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-27 : 06:50:30

select country_name,max(case when country_name='swiss' and curr_code in('EUR','CHF') then 'CHF' ELSE curr_code end) as curr,
sum(base_amount)
from testing
group by country_name

note :use this if all currency codes in that country are listed in the in clause...

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-27 : 06:51:19

or

select country_name,case when country_name='swiss' and curr_code in('EUR','CHF') then 'CHF' ELSE curr_code end as curr,
sum(base_amt)
from testing
group by country_name,case when country_name='swiss' and curr_code in('EUR','CHF') then 'CHF' ELSE curr_code end

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-02-28 : 01:26:54

txs MR.Sakets 2000 its working fine..

quote:
Originally posted by sakets_2000

select country_name,max(case when country_name='swiss' and curr_code in('EUR','CHF') then 'CHF' ELSE curr_code end) as curr,
sum(base_amount)
from testing
group by country_name

note :use this if all currency codes in that country are listed in the in clause...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources