CODEWITHSUNDEEP
mysqljoinsum
Chris Ford
Chris Ford

Reputation: 93

MySQL SUM outputs wrong value in a query consisting multiple joins

I'm getting information on these tables using the following query, however defenderhit and defenderdamage SUM values are getting multiplied with the row count of first join's row number.

Table 'battles':

battle_id   city_id     attacker    defender    battle_time 
1           07          6           0           1342918014

Table 'battlehits':

battle_id   family_id   user_id     hits    damage 
1           0           0           1000    50000
1           6           15          108     3816
1           6           2           81  2046
1           6           1           852 1344

MySQL Query:

SELECT b.battle_id, b.city_id, b.attacker, b.defender, b.battle_time,
SUM(COALESCE(bh1.damage,0)) AS attackerdamage, SUM(COALESCE(bh2.damage,0)) AS defenderdamage,
SUM(COALESCE(bh1.hits,0)) AS attackerhit, SUM(COALESCE(bh2.hits,0)) AS defenderhit
FROM battles AS b
LEFT JOIN battlehits AS bh1 ON b.attacker = bh1.family_id
LEFT JOIN battlehits AS bh2 ON b.defender = bh2.family_id
WHERE b.battle_id=1
GROUP BY b.battle_id LIMIT 1

Result of this query is as following:

battle_id   city_id     attacker    defender    battle_time     attackerdamage  defenderdamage  attackerhit     defenderhit 
1           07          6           0           1342918014      7206            150000          1041            3000

As you can see in the table data, defenderhit and defenderdamage SUM values are supposed to be 1000 and 50000, but they're multiplied by 3. What am I doing in here? What's the problem?

Thanks in advance.

Upvotes: 0

Views: 936

Answers (2)

walrii
walrii

Reputation: 3522

You are getting three rows, before the group by/sum. You have one row for each of the three attacker rows from battlehits. Each of these is paired with the same defender row from battlehits, causing the defender data to be tripled. To see this, remove the group by and limit clauses and take out the sum()s. You are effectively creating the cross product of all defenders X all attackers, and then summing.

This shows the three rows with duplicated defender data. This is a consequence of doing a join on a one to many to many relationship, instead of a one to one to one.

SELECT b.battle_id, b.city_id, b.attacker, b.defender, b.battle_time,
COALESCE(bh1.damage,0) AS attackerdamage, COALESCE(bh2.damage,0) AS defenderdamage,
COALESCE(bh1.hits,0) AS attackerhit, COALESCE(bh2.hits,0) AS defenderhit
FROM battles AS b
LEFT JOIN battlehits AS bh1 ON b.attacker = bh1.family_id
LEFT JOIN battlehits AS bh2 ON b.defender = bh2.family_id
WHERE b.battle_id=1;

Output:

battle_id   city_id attacker    defender    battle_time attackerdamage  defenderdamage  attackerhit defenderhit
1   7   6   0   1342918014  3816    50000   108 1000
1   7   6   0   1342918014  2046    50000   81  1000
1   7   6   0   1342918014  1344    50000   852 1000

You need to split this into separate queries. One for the attacker sums and another for the defender sums.

Upvotes: 1

Vatev
Vatev

Reputation: 7590

You can emulate a SUM of distinct rows by using

(SUM(t.field_to_sum) / COUNT(t.primary_key) * COUNT(DISTINCT t.primary_key))

Upvotes: 1

Related Questions