May 27, 2015 at 12:00 am
Hello experts,
I have records like below, I just need some help with single query to get result below, basically records that has single entry only, which has type '0'
table : temp_test
idtype
c10
c25
c30
c40
c47
c59
c64
c60
c77
c80
c90
Result out of query
idtype
c10
c30
c80
c90
I appreciate your help
May 27, 2015 at 12:38 am
This little snippet returns your expected output:
declare @t table
(
id char(2),
type int
)
insert @t (id, type) values
('c1', 0)
,('c2', 5)
,('c3', 0)
,('c4', 0)
,('c4', 7)
,('c5', 9)
,('c6', 4)
,('c6', 0)
,('c7', 7)
,('c8', 0)
,('c9', 0)
select t.id, SUM(type) from @t t
group by id
having count(*) = 1 and SUM(type) = 0
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 27, 2015 at 10:19 am
i don't think you need to use the count function.
select t.id, SUM(type) from @t t
group by id
having SUM(type) = 0
May 27, 2015 at 10:56 am
Just another option:
declare @t table
(
id char(2),
type int
)
insert @t (id, type) values
('c1', 0)
,('c2', 5)
,('c3', 0)
,('c4', 0)
,('c4', 7)
,('c5', 9)
,('c6', 4)
,('c6', 0)
,('c7', 7)
,('c8', 0)
,('c9', 0);
with basedata as (
select
id,
count(type) cnt
from
@t
group by
id
having
count(type) = 1
)
select
t.*
from
@t t
where
t.type = 0
and exists(select 1 from basedata bd where t.id = bd.id);
May 27, 2015 at 11:20 am
subhashacharya1 (5/27/2015)
i don't think you need to use the count function.
For this particular data set, you are right. But the OP's specs included this requirement:
records that has single entry only, which has type '0'
To make sure that happens, COUNT is very necessary.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply