Discuss MySQL SQL Help: Unique and Group By Question in the MySQL Help forum on Dev Shed. MySQL SQL Help: Unique and Group By Question MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
Posts: 2
Time spent in forums: 36 m 56 sec
Reputation 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.
Posts: 2
Time spent in forums: 36 m 56 sec
Reputation Power: 0
Quote:
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!
Posts: 1,923
Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377
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.
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
