CODEWITHSUNDEEP
mysql
Patrick
Patrick

Reputation: 127

MySQL Query....tricky counts.. not sure how to combine

i need help with this query.. i keep stats for a First Person Shooter.

The data looks like this: https://i.sstatic.net/hEi3n.png

I've am this far with the query:

SELECT
a.name,p.killer as killer_id, ROUND(AVG(p.distance)) as average_distance,p.bambikill,
(Select count(killer)) as total_kills,
(Select count(bambikill) where bambikill = 1)as newbiekills
FROM
player_stats p
inner join account a on p.killer = a.uid
group by killer_id

The result of the enter image description here

What i need to do next is get the deaths of the player 'killer' which woudl be in the victim column and count for all that player's deaths. Each time he's shown up tin the victim column.

Is this possible? I've tried a few combinations but can't seem to get it right. I'm not the best with MySQL queries.

Upvotes: 2

Views: 34

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

One option to determine the number of times a killer has himself been killed is to use a subquery which aggregates over the victim column. Then LEFT JOIN your current query to this subquery. Note that LEFT JOIN is used to allow for the possibility that a killer never in fact gets killed. In this case, COALESCE would report zero for this stat.

SELECT t2.name,
       t1.killer_id,
       t1.average_distance,
       t1.total_kills,
       t1.newbiekills,
       COALESCE(t3.numKills, 0) AS num_times_killed
FROM
(
    SELECT p.killer AS killer_id,
           ROUND(AVG(p.distance)) AS average_distance,
           COUNT(*) AS total_kills,
           SUM(p.bambikill) AS newbiekills
    FROM player_stats p
    GROUP BY p.killer
) t1
INNER JOIN account t2
    ON t1.killer = t2.uid
LEFT JOIN
(
    SELECT victim, COUNT(*) AS numKills
    FROM player_stats
    GROUP BY victim
) t3
    ON t1.killer = t3.victim
GROUP BY t1.killer

Upvotes: 2

Related Questions