How to select count(*) where count < 4

  • 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

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

  • To quote Homer Simpson, 'Doh!'

    Thanks, that's what I needed.

  • select * From Table

    group by ....

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

    :w00t:

  • select * from table

    group by ...

    having count(*)>4

  • 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
  • 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)

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

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