How to select count(*) where count < 4

Question

Post reply

How to select count(*) where count < 4

jej1216

Old Hand

Points: 346
More actions
September 26, 2007 at 3:56 pm

#118356

I can do a select count from a table, no problem:
select BUSINESS_UNIT, ASSET_ID, count(*)
from FS.dbo.PS_AID
group by BUSINESS_UNIT, ASSET_ID
order by BUSINESS_UNIT, ASSET_ID, count (*)
What I cannot come up with is a way to qualify the select for rows whose count is less than 4.
select BUSINESS_UNIT, ASSET_ID, count(*)
from FS.dbo.PS_AID
---- having count(*) < 4 --- this doesn't cut it
group by BUSINESS_UNIT, ASSET_ID
order by BUSINESS_UNIT, ASSET_ID, count (*)
I cannot find direction in the Help files, so I am posting here.
TIA,
jej1216

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

your having clause just needs move after the group by statement and needs to be before the order by statement

jej1216 Old Hand Points: 346 More actions · Answer 2

To quote Homer Simpson, 'Doh!'

Thanks, that's what I needed.

www.robben_11 SSC Enthusiast Points: 188 More actions · Answer 3

select * From Table

group by ....

having count(?)>(select count(?) from Table1)

:w00t:

www.robben_11 SSC Enthusiast Points: 188 More actions · Answer 4

www.robben_11

SSC Enthusiast

Points: 188

December 31, 2010 at 7:32 am

#1268108

select * from table

group by ...

having count(*)>4

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Please note: 3 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004619 More actions · Answer 6

GilaMonster (12/31/2010)
Please note: 3 year old thread.

Heh... and a seriously wrong answer to boot!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

How to select count(*) where count < 4

Cookies on SQLServerCentral