php - SUM all points for each team player in different category -

i need sum of points (hiscore) each player.

for i'm able sum either player1 or player 2. example in category 7, player 1 tom, , in category 5 player 2 tom. result should tom = 2+10 = 12 points.

select sum(subpoints) hiscore, player1_id, player2_id, (     select count(current_record)*2 subpoints, player1_id, player2_id db category_id in (7,8) group player1_id        union     select count(current_record)*10 subpoints, player1_id, player2_id db category_id in (1,2,3,4,5,6) group player1_id ) hi group 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    | 

you can unpivot columns first , aggregate it:

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

see sql fiddle demo.

if integrate original query this, code similar this:

select player, sum(subpoints) hiscore (   select count(current_record)*2 subpoints, player1_id player   db    category_id in (7,8)    group player1_id   union   select count(current_record)*10 subpoints, player2_id player   db    category_id in (1,2,3,4,5,6)    group player2_id ) d group player