Help Needed with Count - 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

Help Needed with Count

Author

Topic

JoepSan
Starting Member

4 Posts

Posted - 2010-05-27 : 04:24:23

Hi,
I am relatively new to SQL programming and need some help with a query.
I hope you can help me.
I try to run a query resulting in displaying some ID's based on a count function with some conditions. I need only those ID’s displayed where the count of variable X and Y exceeds 2 and for whom there are no Z’s stored in the column variable.
Below a simplified example:

ID Variable
1 X
1 Y
1 Z
2 X
2 X
2 Y
3 X

Only ID 2 should be displayed in my final results.
I now have the following:

select ID
from table
where
Variable in ('X’,'Y’)
having count(*) > 2

I however do not know how to add the condition excluding those ID’s with a Z. Just adding a condition -and Variable not in (‘Z’)- obviously does not work. I tried a Union too, but couldn’t get it to work either. Can someone point me in the right direction?
Thanks for your help,
Joep

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 04:33:05

declare @t table (id int, variable char(1))
insert @t
select 1, 'X' union all
select 1, 'Y' union all
select 1, 'Z' union all
select 2, 'X' union all
select 2, 'X' union all
select 2, 'Y' union all
select 3, 'X'

--select * from @t

select id
from @t
where variable <> 'Z' -- not in ('Z') would also work if there is more to exclude...
group by id
having count(*) >2

No, you're never too old to Yak'n'Roll if you're too young to die.

JoepSan
Starting Member

4 Posts

Posted - 2010-05-27 : 04:44:37

Thanks for your help webfred.
However I failed to mention one crucial detail.
My apologies for that.
The column 'variable' is also populated with many other variables, besides X,Y and Z. If I understand correctly your example will also add those in my output if the count exceeds 2, am I correct? I would like to filter only for those results where count(X,y)>2 and not (Z) and excluse any other variables although their count might exceed 2. Am I making sense here?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 04:59:37

Then you have to say which values should be considered:

select id
from @t
where variable in ('X','Y') -- that will exclude all other than X,Y
group by id
having count(*) >2

No, you're never too old to Yak'n'Roll if you're too young to die.

JoepSan
Starting Member

4 Posts

Posted - 2010-05-27 : 05:17:53

I am not sure if I understand..bear with me...I am a newbie :-)
Actually there can be more than 100 variables in this column linked to ID. Do I need to specify all of these?
I am only interested in those ID's where the count of two specific variables (X,Y) exceeds 2 and the count of a third one (Z)equals 0 (as in 'doest not exist for this ID'. All other variables I want to exclude.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 05:40:05

[code]declare @t table (id int, variable char(1))
insert @t
select 1, 'X' union all
select 1, 'Y' union all
select 1, 'Z' union all
select 1, 'X' union all
select 2, 'X' union all
select 2, 'X' union all
select 2, 'Y' union all
select 3, 'X'

select id
from @t as t1
where variable in ('X','Y')
and not exists(select * from @t as t2 where t2.id=t1.id and t2.variable='Z')
group by id
having count(*) >2

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

JoepSan
Starting Member

4 Posts

Posted - 2010-05-27 : 06:56:15

This works.
Thanks for your help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 07:26:12

welcome