Getting a count of records matching a criteria

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)

Getting a count of records matching a criteria

Author

Topic

brandonl
Yak Posting Veteran

58 Posts

Posted - 2004-03-11 : 10:25:13

Hello all!

I have a table, with three fields:

machinename
ID00
Status00

What I need is to get a count of machinenames that have >= 21 Status00's that are 'Applicable'

Here's what I have so far-but it does not seem to be working:

SELECT count(DISTINCT Machinename) AS TotalCount
FROM S_U_D
WHERE Status00 = 'Applicable'
HAVING Count(DISTINCT ID00) >= 21

tia!

~BrandonL

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-11 : 10:30:09

SELECT MachineName, COUNT(DISTINCT ID00) as TotalCount
FROM S_U_D
WHERE Status00 = 'Applicable'
GROUP BY MachineName
HAVING COUNT(DISTINCT ID00) >= 21

Duane.

brandonl
Yak Posting Veteran

58 Posts

Posted - 2004-03-11 : 10:35:53

Duane, thanks for the quick response, but that'll give me a list of machinnames and the count of ID's that are applicable. What I need is a count of machines that have greater than or equal to 21 ID's. Basically-it'll just return a number.

~BrandonL

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-11 : 10:43:34

OK, How about This then:
--************************
SELECT COUNT(*) FROM
(SELECT MachineName, COUNT(DISTINCT ID00) as TotalCount
FROM S_U_D
WHERE Status00 = 'Applicable'
GROUP BY MachineName
HAVING COUNT(DISTINCT ID00) >= 21)
--*******************

IS That what you are looking for?

Duane.

brandonl
Yak Posting Veteran

58 Posts

Posted - 2004-03-11 : 12:26:38

Yes, that worked perfectly. Thanks!

~BrandonL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources