Count (*) having - 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)

Count (*) having

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-03 : 08:08:04

bhmet writes "I group records by account number. Then I want to look at ALL records in the group to make sure a condition does NOT exist in the entire group.

I couldn't find any way to do this other than counting within the group each time the undesired condition DOES exist.

Example
Acct no code
1 2
1 6
1 5
1 9

I want to select accountno when there is no '9' code in the group. The above example should not be selected.

So I've coded:
Select count(code) as code_ct,accountno from table1 where
Code = '9'
group by accountno
having count(code_ct) = 0

I always get 0 recs returned, but when I code
have count(code_ct) = 1
I get the right result. In fact if I code any number other than 0,
I get the right result."

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-03 : 09:07:06

having count(code_ct) = 0
means give me all groups with no rows so you will never get anything.

having max(case when code_ct = 9 then 1 else 0 end) = 1

And I guess you want how many codes there are so

Select count(code) as code_ct,accountno
from table1
group by accountno
having max(case when code_ct = 9 then 1 else 0 end) = 1

==========================================
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.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-03 : 10:24:43

Hi bhmet,

It's hard to understand exactly what you're after without more example data and an example result. Maybe Nigel has what you need, and maybe you're after something more like this...

--data
declare @t table (AcctNo int, code int)
insert @t
          select 1, 2
union all select 1, 6
union all select 1, 5
union all select 1, 9
union all select 2, 1
union all select 2, 2
union all select 2, 5

--calculation
select * from @t where AcctNo not in (select AcctNo from @t where code = 9)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources