MySQL INNER JOIN COUNT() query

Question

I have a database with two tables. One called 'Combatants' with a column 'combatantID'. Another called 'Kills' with columns 'killID', 'shooter' and 'victim'. Both shooter and victim are foreign keys pointing to `Combatants.combatantID'. I would like to make a query that will return data in a table with the form:

combatantID,kills,deaths

I can get it working for either kills or deaths but not both. To do one or the other I've been using this query:

SELECT Combatants.combatantID, COUNT(K1.killID) as kills
FROM Combatants 
INNER JOIN Kills as K1 ON K1.shooter=Combatants.combatantID
GROUP BY Combatants.combatantID

However if I put another INNER JOIN with table Kills as K2 and add COUNT(K2.killID) as deaths I get an odd result that I think is the sum of all player kills, all player deaths instead of player specific kills, deaths.

Any advice would be greatly appreciated. Thanks in advance.

count is group related operation... and try left joins for don't change result rows number — vp_arth
– vp_arth, Commented Apr 8, 2014 at 18:27

fthiella · Accepted Answer · 2014-04-08 18:46:08Z

1

You have two different options, you could count DISTINCT killID:

SELECT
  Combatants.combatantID,
  COUNT(DISTINCT K1.killID) as kills,
  COUNT(DISTINCT k2.killID) as deaths
FROM
  Combatants INNER JOIN Kills as K1 ON K1.shooter=Combatants.combatantID
  INNER JOIN Kills as K2 ON K2.victim=Combatants.combatantID
GROUP BY
  Combatants.combatantID

or you can perform your counts in two different subquery:

SELECT
  Combatants.combatantID,
  k1.kills,
  k2.deaths
FROM
  Combatants INNER JOIN (
    SELECT shooter, COUNT(*) AS Kills
    FROM kills
    GROUP BY shooter
  ) k1 ON k1.shooter = Combatants.combatantID
  INNER JOIN (
    SELECT victim, COUNT(*) AS deaths
    FROM kills
    GROUP BY victim
  ) k2 ON k1.victim = Combatants.combatantID

maybe it's better to use a LEFT JOIN instead of an INNER JOIN, in case a combatant has not shot anyone, or has never been a victim.

edited Apr 8, 2014 at 18:46

answered Apr 8, 2014 at 18:40

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hopperj Over a year ago

Thank you very much! I did go with a LEFT JOIN as you suggested.

Collectives™ on Stack Overflow

MySQL INNER JOIN COUNT() query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related