Counting some data array not work

dear all.can you tell me how to collecting this dat开发者_高级运维a 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.

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

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