Mysql - How to query a table that may have multiple entries in each cell and get a result with single values in each cell

Name       Hobby
John    |  Fishing,开发者_开发百科Traveling
Mary    |  Reading,
Tom     |  Music,
Kate    |  Cooking,Reading
George  |  Traveling,
Peter   |  Fishing,
Lisa    |  Cooking,

I want to select this table

Hobby        Count
Fishing   |  2
Traveling |  2
Reading   |  2
Music     |  1
Cooking   |  2

How can I do that?

---

Use your table properly instead of cramming multiple hobby values into a single column. You should have:

John    |  Fishing
John    |  Traveling
Mary    |  Reading
Tom     |  Music
Kate    |  Cooking
Kate    |  Reading
George  |  Traveling
Peter   |  Fishing
Lisa    |  Cooking

Then you can do:

select hobby
     , count(*) count
  from tablename
 group by hobby
 order by hobby

---

I would do it like @Wes

or if its not possible i'd do it in PHP

$result = array();
$sql = 'SELECT Hobby, count(*) as cnt FROM youttable GROUP BY Hobby';
foreach ($conn->query($sql) as $row) {
    foreach(explode(',',$row['Hobby']) as $hobby) {
        $result[$hobby] += $row['cnt'];
    }
}

---

Generally what you would do is have a separate table for hobbies. So your first table would be consisted of: nameID, name. And your second table would consist of: hobbyID, nameID, description. Once you have it set up like that, you use nameID to link the two tables together.

SELECT * FROM nameTable, hobbyTable WHERE nameTable.nameID = hobbyTable.nameID

And when you have that you can use count() on the hobbyTable to get the quantities.

---

SELECT Hobby, Count(*) FROM myTable ORDER BY Hobby GROUP BY Hobby