CODEWITHSUNDEEP
mysqlsql
punkael
punkael

Reputation: 121

SQL sum a field grouped by same identifier in two different columns

The table has 4 columns Match, Winning_Player_ID, Losing_Player_ID, Quantity_Points_Exchanged_for_that_match. I would like to in a single query show the total number of points each player won and earned over all matches.

Here is the table

M WIN LOSE QTY  
1 100 201 10  
2 201 100 05  
3 100 201 05  
4 302 100 05

For output I would like total it in this way in a single query and cannot figure it out.

ID WIN LOSE
100 15 10
201 05 15
302 05 00

Upvotes: 1

Views: 3690

Answers (3)

Kevin Bedell
Kevin Bedell

Reputation: 13404

Here's the answer:

CREATE THE TABLE:

CREATE TABLE table_name (
  m int,
  win int,
  lose int,
  qty int);

INSERT THE DATA:

INSERT INTO table_name (m, win, lose, qty)
values 
(1, 100, 201, 10),
(2, 201, 100, 05),
(3, 100, 201, 05),
(4, 302, 100, 05);

The QUERY:

SELECT id, sum(won), sum(lost) from (
    SELECT win as id, SUM(qty) as won, 0 as lost 
    FROM table_name W GROUP BY win
  UNION
    SELECT lose as id, 0 as won, SUM(qty) as lost
    FROM table_name W GROUP BY lose
  ) sums
GROUP BY ID

The result:

+------+----------+-----------+
| id   | sum(won) | sum(lost) |
+------+----------+-----------+
|  100 |       15 |        10 |
|  201 |        5 |        15 |
|  302 |        5 |         0 |
+------+----------+-----------+

Upvotes: 0

Glenn
Glenn

Reputation: 9150

No db available to check this out, but perhaps something like this:

SELECT player, SUM(winQty) AS WIN, SUM(loseQty) AS LOSE
  FROM ( SELECT win AS player, qty AS winQty, 0 AS loseQty
           FROM myTable
         UNION ALL
         SELECT lose AS player, 0 AS winQty, qty AS loseQty
           FROM myTable
       ) x
  GROUP BY player

UPDATE: Changed UNION to UNION ALL

Upvotes: 1

zerkms
zerkms

Reputation: 254926

SELECT p.player ID,
       (SELECT SUM(QTY) FROM tbl WHERE WIN = p.player) WIN,
       (SELECT SUM(QTY) FROM tbl WHERE LOSE = p.player) LOSE
FROM
    (SELECT DISTINCT WIN player FROM tbl
    UNION
    SELECT DISTINCT LOSE FROM tbl) p

Upvotes: 1

Related Questions