SQL - Combine two COUNT with different WHERE causes and GROUP BY the users names

Question

I'm trying hard to find a solution to this one but I can't see a way to do it.

I am inserting kills made in a game and I am recording the killer, his clan, the victim and the victim's clan so my database looks like this,

killer | killerClan | victim | victimClan
-----------------------------------------
User_A |   Team_A   | User_B |   Team_B
User_C |   Team_B   | User_A |   Team_A
User_B |   Team_B   | User_A |   Team_A
User_D |   Team_A   | User_C |   Team_B
User_C |   Team_B   | User_A |   Team_A
User_B |   Team_B   | User_D |   Team_A
User_D |   Team_A   | User_C |   Team_B

The same user can be shown as a killer or a victim depends if he killed somebody or died by somebody.

I can show the total kills of users of a specific Clan by,

SELECT killer AS username, COUNT(killer) AS kills FROM master_tracker
WHERE killerClan = "Team_A"
GROUP BY killer
ORDER BY kills DESC

and by the same logic, I can show the total deaths of users of a specific Clan by,

SELECT victim AS username, COUNT(victim) AS deaths FROM master_tracker
WHERE victimClan = "Team_A"
GROUP BY victim
ORDER BY deaths DESC

But... what I want to do is to combine the two queries and show the users' kills and deaths group by their name which can be found in killer column in the first case and victim column in the second case so ultimately to be able to present their Kills/Deaths Ratio but I need different WHERE causes because in first case their Clan can be found as killerClan and in the second case as victimClan.

Goal is to have a result like that.

username | kills | deaths | kdRatio
-----------------------------------
 User_D  |   2   |    1   |    1
 User_A  |   1   |    3   |   -2

All suggestions are welcomed.

GMB · Accepted Answer · 2020-02-13T00:39:06.167

1

You can use union all and aggregation:

select 
    username, 
    sum(kills) kills, 
    sum(deaths) deaths,
    sum(kills) - sum(deaths) kdRatio
from (
    select killer username, count(*) kills, 0 deaths 
    from master_tracker 
    where killerClan = 'TeamA'
    group by killer
    union all 
    select victim, 0, count(*) 
    from master_tracker 
    where victimClan = 'TeamA'
    group by victim
) t
group by username
order by kdRatio desc

edited Feb 13 '20 at 00:39

answered Feb 13 '20 at 00:03

GMB

216,147
25
84
135

Thank you for your answer. My database column names are **killer, killerClan, victim, victimClan** I have to get the username by combining **killer** and **victim** columns where the user is the same. Also, I need to present results according to specific Clan which can also be found as killerClan or victimClan. I don't have kills and killed columns so to find the total Kills and Deaths I have to COUNT rows where Clan name is ex. Team_A and Group by user. – Catalyst Feb 13 '20 at 00:12
@GeorgeTagas: yes. The above query should produce the expected results (ie, number of kills and deaths per user). Please try the query. – GMB Feb 13 '20 at 00:28
I'm getting the correct sum(kills) while I'm getting sum(deaths) only when user has been killed by a user of the TeamA, not from users of other teams. – Catalyst Feb 13 '20 at 00:38
@GeorgeTagas: my bad. Fixed. – GMB Feb 13 '20 at 00:39
1

Thank you very much! Exactly what I wanted! I was starting to lose hope because this post was tagged as similar to [this one](https://stackoverflow.com/questions/33139000/multiple-query-same-table-but-in-different-columns-mysql) by [Barmar](https://stackoverflow.com/users/1491895/barmar) but I really couldn't find my way out of this issue looking there. – Catalyst Feb 13 '20 at 00:44

SQL - Combine two COUNT with different WHERE causes and GROUP BY the users names

1 Answers1