Post reply

Return Records with Count > 2

  • April 19, 2005 at 2:18 pm

    #164237

    This is probably an easy and obvious problem, but I can't get the right syntax.

    Select a.Company, a.Contact, a.CustomerNumber, a.State, a.City From Table a

    I only want to return records where a count(*) of CustomerNumber and State are more than 1.  I cannot use the Group function since the City would be different.

     

    Thanks!

     

     

  • April 19, 2005 at 2:29 pm

    #553589

    I have no idea of what is unique on your data but this is a start

    Select a.Company, a.Contact, a.CustomerNumber, a.State, a.City From

        Table a

    join

        (

         select CustomerNumber, State 

         from TableA

         group by CustomerNumber, State

         having Count(*) >2 ) SubQ

         on SubQ.CustomerNumber = a.CustomerNumber and  SubQ.state = a.state

     

    hth


    * Noel

  • April 20, 2005 at 9:58 am

    #553831

    Thanks Noel.  That makes perfect sense and should work.  I appreciate the help!

  • April 20, 2005 at 10:04 am

    #553833

    Cheers!


    * Noel

Viewing 4 posts - 1 through 3 (of 3 total)

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