Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to select count(*) where count < 4 Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2007 3:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 1, 2009 11:12 AM
Points: 2, Visits: 6
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

Post #403243
Posted Wednesday, September 26, 2007 4:01 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 1,158, Visits: 1,029
your having clause just needs move after the group by statement and needs to be before the order by statement
Post #403245
Posted Wednesday, September 26, 2007 4:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 1, 2009 11:12 AM
Points: 2, Visits: 6
To quote Homer Simpson, 'Doh!'

Thanks, that's what I needed.
Post #403246
Posted Friday, December 31, 2010 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 7, 2011 4:56 PM
Points: 2, Visits: 1
select * From Table
group by ....
having count(?)>(select count(?) from Table1)
Post #1041385
Posted Friday, December 31, 2010 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 7, 2011 4:56 PM
Points: 2, Visits: 1
select * from table
group by ...
having count(*)>4
Post #1041387
Posted Friday, December 31, 2010 8:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
Please note: 3 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1041393
Posted Friday, December 31, 2010 11:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 35,766, Visits: 32,428
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1041509
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse