CODEWITHSUNDEEP
mysqljoinsum
phew
phew

Reputation: 826

MySQL how to SUM() columns of two JOINed tables into a new column?

Good morning/evening everybody,

I am trying to (LEFT) JOIN two tables into a table and SUM() specific columns' values of the matching ON fk_id = id... statement. This is what the tables look like:

ws1 table:

ws1 table

ws2 table:

ws2 table

The queries I have tried so far:

SELECT
    alias.name alias,   
    (SUM(IFNULL(ws1.teamkills,0)) + SUM(IFNULL(ws2.teamkills,0))) teamkills
FROM pickup
    JOIN player ON player.pickup_id = pickup.id
    JOIN alias ON player.alias_id = alias.id
    LEFT JOIN weapon_stats_1 ws1 ON ws1.pickup_id = pickup.id AND ws1.player_id = player.id
    LEFT JOIN weapon_stats_2 ws2 ON ws2.pickup_id = pickup.id AND ws2.player_id = player.id
WHERE pickup.logfile_name = 'srv-20130725-2151-log' GROUP BY player.id

Result:

query result

and:

SELECT
    alias.name alias,   
    (SUM(DISTINCT IFNULL(ws1.teamkills,0)) + SUM(DISTINCT IFNULL(ws2.teamkills,0))) teamkills
FROM pickup
    JOIN player ON player.pickup_id = pickup.id
    JOIN alias ON player.alias_id = alias.id
    LEFT JOIN weapon_stats_1 ws1 ON ws1.pickup_id = pickup.id AND ws1.player_id = player.id
    LEFT JOIN weapon_stats_2 ws2 ON ws2.pickup_id = pickup.id AND ws2.player_id = player.id
WHERE pickup.logfile_name = 'srv-20130725-2151-log' GROUP BY player.id

Result:

Query result

I understand that SUM(DISTINCT.... ) returns 2, because DISTINCT selects only one result of the same value.

My goal is to get SUM()s of both teamkills fields and add them together. In the example it should return 3 where player_id is 4. How can I do that?

EDIT:

Table 'player':

enter image description here

Table 'pickup':

enter image description here

Upvotes: 0

Views: 3870

Answers (3)

peterm
peterm

Reputation: 92785

A possible solution without using correlated subqueries

SELECT a.name alias, SUM(q.teamkills) teamkills
  FROM
(
  SELECT player_id, teamkills
    FROM weapon_stats_1 w JOIN pickup p
      ON w.pickup_id = p.id
   WHERE p.logfile_name = 'srv-20130725-2151-log'
   UNION ALL
  SELECT player_id, teamkills
    FROM weapon_stats_2 w JOIN pickup p
      ON w.pickup_id = p.id
   WHERE p.logfile_name = 'srv-20130725-2151-log'
) q JOIN player p
    ON q.player_id = p.id JOIN alias a
    ON p.alias_id = a.id
 GROUP BY a.name

Sample output:

|  ALIAS | TEAMKILLS |
----------------------
| alias4 |         3 |

Here is SQLFiddle demo

Upvotes: 0

Md. Mahabubur Rahman
Md. Mahabubur Rahman

Reputation: 820

You may something like following

Table t1

 CREATE TABLE `t1` (
      `pik_id` int(11) NOT NULL AUTO_INCREMENT,
      `palyer_id` int(11) DEFAULT NULL,
      `amount` double DEFAULT NULL,
      UNIQUE KEY `pik_id` (`pik_id`)
    )

 ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Table t2

    CREATE TABLE `t2` (
  `playayer_id` int(11) NOT NULL AUTO_INCREMENT,
  `amount` double DEFAULT NULL,
  UNIQUE KEY `playayer_id` (`playayer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

and The query for join and SUM 

SELECT playayer_id, t1.amount+t2.amount amount
FROM
(SELECT t1.pik_id,t1.palyer_id,SUM(t1.amount) amount FROM t1 GROUP BY t1.palyer_id)t1
JOIN 
(SELECT t2.playayer_id,t2.amount FROM t2)t2
ON t1.palyer_id=t2.playayer_id
GROUP BY playayer_id


    playayer_id amount
           1    133
           2    152
           3    1076

and I hope your problem will solved by this way.

Upvotes: 0

krokodilko
krokodilko

Reputation: 36087

You need two dependent subqueries instead of join of ws1+ws2, jonin wont work here.
Something like:

SELECT id, player_alias,
       ( SELECT sum( teamkills ) FROM ws1
         WHERE ws1.player_id = player.id )
        +
       ( SELECT sum( teamkills ) FROM ws2
         WHERE ws2.player_id = player.id ) as total
FROM player
JOIN alias ON ......

Here is SQLFiddle demo, look at the first query (and the resultset below) to gain better understanding why you get wrong results from join, and in general, how joins work.

Join combines (glues) each record from one table to all corresponding records from the other table (that meet join criteria), and in your case it produces 4 rows with duplicated data.

The third query in this demo is an example of dependent subqueries that gives proper result (for example data in this demo).

Upvotes: 2

Related Questions