Help writing a Condition/Case - 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 2005 Forums

Transact-SQL (2005)

Help writing a Condition/Case

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2011-01-27 : 14:10:11

I have table in the following format

create table #test(id int , amount int, flag int, code varchar(2))

insert into #test (id,amount,flag,code)
select 1,10, 9, 'x' union all
select 1,10, 9, 'y' union all
select 2,20, 9, 'x' union all
select 2,20, 8, 'y' union all
select 3,30, 8, 'z'

select * from #test

id amount flag code
----------- ----------- ----------- ----
1 10 9 x
1 10 9 y
2 20 9 x
2 20 8 y
3 30 8 z

I need to add up the amounts across the IDs at codelevel.

SELECT distinct code,
SUM(isnull(Amount,0)) Amount
from #test
GROUP BY
code

code Amount
---- -----------
x 30
y 30
z 30

Now i need to filter out amounts related to a certain flag.

I need to add an where condition to consider amount = 0 where flag = 8

my final out put should be.

Code Amount
----------- -----------
X 30
Y 10
X 0

Please help me out on how to handle this scenario. Thanks in advance.

ZZartin
Starting Member

30 Posts

Posted - 2011-01-27 : 15:05:10

Try this for the SUM

SUM(isnull(CASE WHEN flag = 8 THEN 0 ELSE Amount END,0))