How to get total for each category for every user?

Question

Having trouble with this query:

I have a table that has an entry for each battle a user has entered.

user_id | name   | category | points
==================================
1       | user1  | battle1  | 4
54      | user2  | battle1  | 20
88      | user 8 | battle4  | 33
etc

There are 4 categories.

I hope I can explain this properly.

What I need to do is get the sum of each category for each user but also show the totals for that user from the other categories as well.

Right now I can query the sum of one category like this per user:

SELECT user_id, name, category,  SUM(points) 
FROM battle_points 
WHERE user_id = id 
GROUP BY category

But I don't want it per user. So if I do that query without the WHERE clause it's all messed up.

I need to have the top 10 ranks for each category. So it could be like this:

name  | cat 1 | cat 2 | cat 3 | cat 4
=====================================
user1 | 33    | 0     | 49    | 5
user2 | 0     | 55    | 12    | 3
etc...

Any ideas?

Thanks.

battle1 in your table is results cat 1? battle2 is cat 2 and so on?? — Raymond Nijland, Sep 19 '17 at 12:38
This https://stackoverflow.com/questions/1241178/mysql-rows-to-columns can help! — M Reza Saberi, Sep 19 '17 at 12:40
Seriously consider handling issues of data display in application code. — Strawberry, Sep 19 '17 at 12:55

score 0 · Accepted Answer · answered Sep 19 '17 at 12:42

You can use something like this: (I changed name of your table and for simplify my test I used CATEGORY as INT, but you can easily change it)

CREATE TABLE BAT_POI (USER_ID INTEGER, NAME VARCHAR(20), CATEGORY INT, POINTS INT);
INSERT INTO BAT_POI VALUES (1,'user1',1,4);
INSERT INTO BAT_POI  VALUES (54,'user2',1,20);
INSERT INTO BAT_POI  VALUES (88,'user8',4,33);
INSERT INTO BAT_POI  VALUES (1,'user1',3,49);

SELECT USER_ID, NAME
        , SUM( CASE WHEN CATEGORY=1 THEN POINTS ELSE 0 END ) AS CAT1
        , SUM( CASE WHEN CATEGORY=2 THEN POINTS ELSE 0 END ) AS CAT2
        , SUM( CASE WHEN CATEGORY=3 THEN POINTS ELSE 0 END ) AS CAT3
        , SUM( CASE WHEN CATEGORY=4 THEN POINTS ELSE 0 END ) AS CAT4
FROM BAT_POI
GROUP BY USER_ID, NAME;

Output:

USER_ID NAME    CAT1    CAT2    CAT3    CAT4
1       user1   4       0       49      0
54      user2   20      0       0       0
88      user8   0       0       0       33

This one works beautifuly! I added: ORDER BY cat1 desc limit 10 To the end and it shows nicely. Thanks! — , Sep 19 '17 at 13:05

How to get total for each category for every user?

1 Answers1