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: Monday, July 21, 2014 7:33 AM
Points: 1,140, Visits: 963
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 @ 3:14 AM
Points: 42,460, Visits: 35,520
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:58 AM
Points: 36,756, Visits: 31,213
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