SQL Q: What the best way to count most occurences in a column?

Discussion in 'OT Technology' started by DeeVoc, Mar 13, 2003.

  1. DeeVoc

    DeeVoc Heh.

    Joined:
    Aug 13, 2001
    Messages:
    33,124
    Likes Received:
    0
    Location:
    Brooklyn
    Like if I have a relation of suppliers and I want to find the supplier that appears the most in the relationship. I feel like I should be using count but that only returns the number or all types of instances.
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    You have to group the suppliers, then count the instances. Or if you only want one record returned you max(count(partID)).
     
  3. DeeVoc

    DeeVoc Heh.

    Joined:
    Aug 13, 2001
    Messages:
    33,124
    Likes Received:
    0
    Location:
    Brooklyn
    Thanks, of course I get it 4 minutes after I break down and ask.

    Okay, one more question, how do I display the count of something along with other information since count doesn't like beign put in a select line with anything else.
     
  4. DeeVoc

    DeeVoc Heh.

    Joined:
    Aug 13, 2001
    Messages:
    33,124
    Likes Received:
    0
    Location:
    Brooklyn
    Okay, nevermind again. I finally figured out that you have to group by a trait that you want to use in a select statement.
     

Share This Page