MySQL Count in select

Question

i have a MySQL table with the username and bought items. Like…

Username1 - Item_A
Username1 - Item_A
Username2 - Item_C
Username2 - Item_D

I need to count how many differed Itemy every user has bought.

Like:

Username1 - 1
Username2 - 2

user_2422776 · Answer

You can group by the user and apply a distinct count to the items:

SELECT   username, COUNT(DISTINCT item)
FROM     mytable
GROUP BY username