MySQL query - counting values in query

Question

here's my 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 to do a query which sums the categories in order to get this result:

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

what's the best method? thanks

EDIT: extended approach (sorry my mistake)

my table as follows:

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

how can i query it to get this result:

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

Answer 1

If you know for sure that there are only 3 categories you 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
from table
group by user