database - MySQL query - counting values in query -

here's example table (assuming i'm having 3 categories only):

id user    item    category ---------------------------- 1  myuser  item1   1 2  myuser  item2   2 3  myuser  item3   2 4  myuser  item4   2 5  myuser  item5   2 6  myuser  item6   3 

i'm trying query sums categories in order result:

user    cat_1   cat_2   cat_3 -------------------------------- myuser  1       4       1 

what's best method? thanks

edit: extended approach (sorry mistake)

my table follows:

id user    item    category  total ------------------------------------ 1  myuser  item1   1         2 2  myuser  item2   2         6 3  myuser  item3   3         4 

how can query result:

user    cat_1   cat_2   cat_3 -------------------------------- myuser  2       6       4 

if know sure there 3 categories can go with:

select     user,     sum(if(category=1,1,0)) cat_1,     sum(if(category=2,1,0)) cat_2,     sum(if(category=3,1,0)) cat_3 table group user