CODEWITHSUNDEEP
phpmysql
zbuzanic
zbuzanic

Reputation: 3

SUM all points for each team player in different category

I need to do a SUM of all points (hiscore) for each player.

For now I'm able to sum either player1 or player 2. So for example in category 7, player 1 is Tom, and in category 5 player 2 is also Tom. Result should be Tom = 2+10 = 12 points.

SELECT SUM(subpoints) AS hiscore, player1_id, player2_id, FROM (
    SELECT COUNT(current_record)*2 AS subpoints, player1_id, player2_id FROM db WHERE category_id IN (7,8) GROUP BY player1_id
       UNION ALL
    SELECT COUNT(current_record)*10 AS subpoints, player1_id, player2_id FROM db WHERE category_id IN (1,2,3,4,5,6) GROUP BY player1_id
) AS hi
GROUP BY player1_id

Sample table:

 category_id | player1 | player2 | subpoints |
-------------+---------+---------+-----------+
           7 |   Tom   |  Mike   |     2     |
           5 |  Peter  |  Tom    |    10     |
----------------------------------------------

Final result should be:

  Player | hiscore |
  -------+---------+
  Tom    |   12    |
  Mike   |    2    |
  Peter  |   10    |

Upvotes: 0

Views: 64

Answers (1)

Taryn
Taryn

Reputation: 247690

You can unpivot the columns first and then aggregate it:

select player, sum(subpoints) hiscore
from
(
  select category_id, player1 player, subpoints
  from db
  union all
  select category_id, player2 player, subpoints
  from db
) d
group by player;

See SQL Fiddle with Demo.

If you integrate your original query into this, then the code will be similar to this:

select player, sum(subpoints) hiscore
from
(
  SELECT COUNT(current_record)*2 AS subpoints, player1_id as player
  FROM db 
  WHERE category_id IN (7,8) 
  GROUP BY player1_id
  UNION ALL
  SELECT COUNT(current_record)*10 AS subpoints, player2_id as player
  FROM db 
  WHERE category_id IN (1,2,3,4,5,6) 
  GROUP BY player2_id
) d
group by player

Upvotes: 1

Related Questions