September 10th, 2012, 01:42 PM
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.
I'm a novice SQL programmer so struggling where to start... any advice or direction would be appreciated.
September 10th, 2012, 01:58 PM
Use a COUNT() + DISTINCT to get the count and a GROUP BY to make it count according to the fruit type.
SELECT type, COUNT(DISTINCT food) FROM table GROUP BY type
September 10th, 2012, 02:05 PM
Originally Posted by requinix
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!
September 10th, 2012, 05:00 PM
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...