Query Problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Query Problem

Author

Topic

jpfo
Starting Member

7 Posts

Posted - 2006-07-13 : 06:51:59

Hi,
I've the follow table, eg

CODE1 VALUE1 CODE2 VALUE2
----- ------ ----- ------
AAA....100.....BBB....25....
AAA....60......CCC....50....

and i want to get something like:

SELECT SUM(VALUE1),SUM(VALUE2)

but only for:
VALUE1 if CODE1=AAA
VALUE2 if CODE2=CCC

Doing 2 queries and running it apart its no problem, but trying to get one query its beeing a noob fight, since if i put the condiction

WHERE (CODE1='AAA' OR CODE2='CCC')

I keep getting SUM(VALUE2) equal to 75, and i just want 50

Can anyone gimme some enlightment, pls.

Tks in advance,
JP

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-13 : 06:54:21

can you post the expected result set ?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-13 : 07:09:30

select sum(case when code1 = 'AAA' then value1 else 0 end), sum(case when code2 = 'CCC' then value2 else 0 end)
from tbl
where code1 = 'AAA'
or code2 = 'CCC'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-13 : 07:14:00

Try this...

select sum(value1) as Value1, sum(value2) as Value2
from
(
select sum(value1) as value1, 0 as value2
from #test
where code1 = 'AAA'
union all
select 0 as value1, sum(value2) as value2
from #test
where code2 = 'CCC') as a

Harsh Athalye
India.
"Nothing is Impossible"

jpfo
Starting Member

7 Posts

Posted - 2006-07-13 : 08:03:53

Tks nr
Thats what i was expecting.

Tks also to all helpers.

Best regards to all
JP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources