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

Mark · Accepted Answer · 2014-02-06 02:31:29Z

3

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

edited Feb 6, 2014 at 2:31

Mark

8,47115 gold badges52 silver badges81 bronze badges

answered Feb 6, 2014 at 2:23

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fuxi Over a year ago

christian, sorry to bother you again, i had a mistake in my task, i need to display the totals instead .. :/ i think the modified syntax should be: sum(if(category=1,total,0)) as cat_1 ?

Iłya Bursov Over a year ago

@Fuxi yes, replace 1 with column name (total in your case)

Fuxi Over a year ago

thanks a lot .. didn't know about the IF syntax will help me a lot :)

Collectives™ on Stack Overflow

MySQL query - counting values in query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related