Count frequency of values in rows and columns SQL

user_11972730 0
Viewed 985

I have an SQLite database and I am trying to count the occurrence of values in a column but also values in a corresponding row.

My dataset is:

participant_id  game_id      win   selected_champion

    2           3640481414    0     Yasuo
    7           3643429174    0     Gangplank
    10          3643470467    1     Jhin
    5           3643669725    0     Rammus
    10          3643707347    1     Talon
    10          3644255324    1     Sivir
    5           3644305477    1     Sion
    8           3644626709    1     Shaco
    5           3645020045    1     Warwick
    10          3645063546    0     Jhin

and the expected output would be something like

selected_champion   games_played  games_won

Gangplank                1            1
Jhin                     2            1
Rammus                   1            0

I am able to get the number of games played with the query

SELECT 
    selected_champion,
    count(selected_champion) as 'games_played' 
FROM (myTable) GROUP BY selected_champion

But I can't seem to figure out how to count the number of times win = 1 compared to the champion.

pivot count group-by sqlite sql
2 Answers

You can just sum it because it takes on the values 0 and 1:

SELECT selected_champion, COUNT(*) as games_played, SUM(win) as games_won
FROM myTable
GROUP BY selected_champion;

The more general solution is to convert it to a boolean:

SUM( win = 1 )

Or a version that works in any database:

SUM(CASE WHEN win = 1 THEN 1 ELSE 0 END)
user_11440350

You can do conditional aggregation:

select 
    selected_champion,
    count(*) games_played,
    sum(win) games_won
from mytable
group by selected_champion

This works because win contains 0s and 1s only. If it was a string like 'won', 'lost', you would do:

select 
    selected_champion,
    count(*) games_played,
    sum(win = 'won') games_won
from mytable
group by selected_champion

Side note: do not use single quotes for identifiers. Although some databases may accept it, single quotes should be reserved for literal strings only. Use the proper quoting character for your database (in SQLite, double quotes are preferred), or better yet use identifiers that do not require quoting.

user_106767160
Related