hw to write sql conditions in select query

Posted by Oswaldlily under Sql Server on 3/10/2014 | Points: 10 | Views : 701 | Status : [Member] | Replies : 4
CardNo type
1000 X
2000 X
3000 Y
500 Y


if type='X' then sum(CardNO)
else
cardNo

expected o/p
CardNo type
3000 X
3000 Y
500 Y

How to write this in single select query..
where only row by row gets checked..




Responses

Posted by: Bandi on: 3/10/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
SELECT distinct CASE WHEN type = 'Y' THEN cardNo ELSE SUM(case when type = 'X' then Cardno END) OVER() end
,type
FROM TableName


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 3/10/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
hi Vijila,

If you get solution from posted responses please mark those responses as answer by clicking on "Mark this response as answer " link

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Brahmam on: 3/11/2014 [Member] Starter | Points: 25

Up
0
Down


select distinct cardno=(CASE type
WHEN 'x' THEN SUM(case when type = 'X' then Cardno END) OVER()
else cardNo
END),type from card



brahmam

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saranpselvam on: 4/4/2014 [Member] Starter | Points: 25

Up
0
Down
-- 1
create table test3 (number int,cosde varchar(10))
-- 2
insert into test3
select 1000,'X'
union
select 4000,'X'
union
select 3000,'Y'
union
select 1000,'Y'
union
select 5000,'z'

-- 3
select * from test3

-- 4
select distinct cosde,
case when cosde ='X' then sum(case when cosde ='X' then number end) OVER() else number end
from test3


Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response