Counting some data array not work

Question

dear all.can you tell me how to collecting this data in mysql:

item        colour
nut         brown
nut         black

i want the result like:

item       colour_qty
nut         2

and i have try like this, but didn't work:

SELECT item, COUNT(colour) AS colour_qty FROM mytable

How do i do that?thanks.

Answer 1

Add a group by clause at the end (GROUP BY item) to get the proper results.

SELECT item, COUNT(colour) AS colour_qty FROM mytable GROUP BY item

Answer 2

SELECT item, COUNT(colour) AS colour_qty
FROM mytable
GROUP BY item