Clever Geek Handbook

Number of columns 2

So, basically, I need a query that will display the display name, kills and deaths.

I have two tables that I need to extract from.

Two tables:

player 

id      | name
2334324 | user
4353454 | user2

where id

is their unique identifier and name

is their display name.

Second table:

player_kill 

id | killer  | victim  |
1  | 2334324 | 4353454 |
2  | 2334324 | 4353454 |  
3  | 4353454 | 2334324 |

where columns killer

/ victim

contain a unique identifier for the table player

.

I would like to be able to count the occurrences of the player id in killer

and victim

for the query to return:

name | kills | deaths
user | 2     | 1
user2| 1     | 2

where the number under murders will be the number of occurrences it playerid

has in the column killer

, and also for deaths

I hope I have provided enough information.

What I have so far:

SELECT `player`.`name`, COUNT(DISTINCT `player_kill`.`id`) as `kills`, COUNT(DISTINCT `player_kill`.`id`) as `deaths`
FROM `player`
LEFT JOIN `player_kill` ON `player`.`id`=`player_kill`.`killer`
LEFT JOIN `player_kill` ON `player`.`id`=`player_kill`.`victim`
WHERE `player`.`id` = `player_kill`.`killer` AND `player`.`id` = `player_kill`.`victim`
GROUP BY `player`.`id`;
+3


source to share


2 answers


Try

SELECT 
    p.name, 
    count(distinct pk1.id) as kills, 
    count(distinct pk2.id) as deaths
FROM player p
LEFT JOIN player_kill pk1 ON pk1.killer = p.id 
LEFT JOIN player_kill pk2 ON pk2.victim = p.id 
group by p.name


http://sqlfiddle.com/#!9/649504/15/0

+3


source


See if this works:

SELECT  `player`.`name`,
        COUNT(DISTINCT k.`id`) as `kills`,
        COUNT(DISTINCT v.`id`) as `deaths`
    FROM  `player`
    LEFT JOIN  `player_kill` AS k ON `player`.`id` = k.`killer`
    LEFT JOIN  `player_kill` AS v ON `player`.`id` = v.`victim`
    GROUP BY  `player`.`id`;


If not, then we might need to do COUNT into subqueries.

+3


source






More articles:


All Articles