CODEWITHSUNDEEP
androidsqldatabasesqlite
domen
domen

Reputation: 1279

Counting multiple value occurrences

I have a table like this in SQLLite database. Name1 is the name of player 1 (same for name2) and winner represents which player won (Ex. first row, JOE won).

I would like to get all the names of opponents for a specific player, number of wins where the player won against that player and how many times have they played.

Ex. output for JOE:

name    wins    games
----------------------  
BILL    1       2     (JOE played againts BILL 2 times and JOE won 1)
NICK    2       2
GREG    1       3     (JOE played againts GREG 3 times and JOE won 1)

This is what I have so far, but it only outputs the name of all players:

SELECT name2 FROM games WHERE name1="JOE"
UNION
SELECT name11 FROM games WHERE name2="JOE"

Data in the table games:

id  name1       name2     winner  
----------------------------------------
1   JOE          BILL       1
2   BILL         JOE        1
3   NICK         JOE        2
4   JOE          NICK       1
5   NICK         BILL       1
6   GREG         JOE        1
7   GREG         JOE        2
8   GREG         JOE        1

Upvotes: 2

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

Here is an approach with aggregation and case statements. Calculating the winner for each game is a bit tricky, because the winner refers to either the name1 or name2 column. You seem to want the wins for the opponent, so this logic makes sure that the winner is not referring to JOE:

select (case when name1 = 'JOE' then name2 else name1 end) as name,
       sum(case when name1 = 'JOE' and winner = 2 then 1
                when name2 = 'JOE' and winner = 1 then 1
                else 0
           end) as wins,
       count(*) as games
from games g
where 'JOE' in (name1, name2)
group by (case when name1 = 'JOE' then name2 else name1 end);

Upvotes: 4

Related Questions