#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    MySQL SQL Help: Unique and Group By Question


    I have a table with, as an example, the following contents:
    column: type food
    ROW1 : fruit orange
    ROW2 : veg tomato
    ROW3 : fruit apple
    ROW4 : fruit banana
    ROW5 : veg spinach
    ROW6 : veg spinach
    ROW7 : fruit tomato

    I am trying to come up with a query to return the unique types with the unique counts per.

    example output:
    type total
    ----- -----
    fruit 4
    veg 2

    I'm a novice SQL programmer so struggling where to start... any advice or direction would be appreciated.
  2. #2
  3. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,749
    Rep Power
    9397
    Use a COUNT() + DISTINCT to get the count and a GROUP BY to make it count according to the fruit type.
    Code:
    SELECT type, COUNT(DISTINCT food) FROM table GROUP BY type
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by requinix
    Use a COUNT() + DISTINCT to get the count and a GROUP BY to make it count according to the fruit type.
    Code:
    SELECT type, COUNT(DISTINCT food) FROM table GROUP BY type

    Thanks so much for your quick reply! I thought I was onto the right query, but couldn't get the logic right. You just saved me an hour of struggling with this!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    Note that without a Primary Key, strictly speaking this isn't really a table, just as a the fruit of the tomato plant isn't, stictly speaking, a vegetable.

    I'll get my coat...

IMN logo majestic logo threadwatch logo seochat tools logo