SQL - How to count the number of wins for each player-CodePudding

I'm new to learning SQL and was wondering how to sort this data from Khanacademy. What I want to do is to show the number of wins from each player. So far, I have been able to show the number of each games that each player has participated in and show who won in each game. However, I want to count how many wins each player has.

Can someone experienced examined the queries and help guide me?

CREATE TABLE card_games(id INTEGER PRIMARY KEY AUTOINCREMENT,
    date_played TEXT,
    game_name TEXT,
    player_name TEXT,
    score INTEGER);

INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/07','Rummy','Spunky Sam',226);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/07','Rummy','Marcimus',418);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/07','Rummy','Winston',523);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/07','Rummy','Hopper',311);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/14','Go Fish','Spunky Sam',7);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/14','Go Fish','Marcimus',5);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/14','Go Fish','Winston',4);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/14','Go Fish','Hopper',10);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/14','Crazy Eights','Spunky Sam',215);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/14','Crazy Eights','Marcimus',167);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/14','Crazy Eights','Winston',109);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/14','Crazy Eights','Hopper',192);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/21','Rummy','Spunky Sam',473);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/21','Rummy','Marcimus',324);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/21','Rummy','Hopper',516);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/28','Crazy Eights','Spunky Sam',119);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/28','Crazy Eights','Marcimus',212);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/28','Crazy Eights','Purple Pi',314);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/01/28','Crazy Eights','Hopper',252);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/04','Go Fish','Spunky Sam',3);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/04','Go Fish','Marcimus',11);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/04','Go Fish','Winston',12);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/04','Go Fish','Hopper',0);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/04','Pitch','Spunky Sam',17);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/04','Pitch','Marcimus',22);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/04','Pitch','Winston',-3);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/04','Pitch','Hopper',9);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/11','Rummy','Amelia',525);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/11','Rummy','Marcimus',419);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/11','Rummy','Winston',316);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/11','Rummy','Hopper',398);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/18','Crazy Eights','Spunky Sam',119);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/18','Crazy Eights','Marcimus',231);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/18','Crazy Eights','Winston',153);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/18','Crazy Eights','Hopper',175);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/25','Pitch','Spunky Sam',12);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/25','Pitch','Marcimus',6);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/25','Pitch','Winston',21);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/25','Go Fish','Spunky Sam',6);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/25','Go Fish','Marcimus',7);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/02/25','Go Fish','Winston',13);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/04','Rummy','Spunky Sam',378);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/04','Rummy','Marcimus',327);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/04','Rummy','Winston',413);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/04','Rummy','Hopper',517);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/11','Pitch','Spunky Sam',-1);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/11','Pitch','Marcimus',-5);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/11','Pitch','Winston',7);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/11','Pitch','Hopper',22);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/18','Crazy Eights','Spunky Sam',91);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/18','Crazy Eights','Marcimus',153);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/18','Crazy Eights','Amelia',174);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/18','Crazy Eights','Mr. Pink',216);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/25','Rummy','Spunky Sam',416);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/25','Rummy','Marcimus',505);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/25','Rummy','Winston',397);
INSERT INTO card_games(date_played,game_name,player_name,score) VALUES ('2015/03/25','Rummy','Hopper',443);

SELECT * FROM card_games;

SELECT player_name,
SUM(CASE
WHEN player_name = "Spunky Sam" THEN 1 
WHEN player_name = "Marcimus" THEN 1
WHEN player_name = "Amelia" THEN 1
WHEN player_name = "Hopper" THEN 1
WHEN player_name = "Mr. Pink" THEN 1
WHEN player_name = "Purple Pi" THEN 1
WHEN player_name = "Winston" THEN 1
END) AS "GamesPlayed"
FROM card_games
GROUP BY player_name;

SELECT date_played, game_name,player_name AS "Winner", MAX(score)
FROM card_games
GROUP BY date_played, game_name

CodePudding user response：

You can achieve your solution using CTE. Here is the code

with cte1 as(
  SELECT date_played, game_name,player_name AS Winner, MAX(score)
  FROM card_games
  GROUP BY date_played, game_name,player_name
     )
     select winner,count(1) from cte1
     group by winner

CodePudding user response：

I had some problems with the code that you posted so I created a SQL Fiddle to work on it with some slight syntax changes. I suspect you might actually be working in MySQL but I've converted this code to MSSQL (Microsoft SQL), sorry about that. But the logic remains the same.

So, yes. When you get into aggregate functions you start to find that acquiring more than one piece of data as part of an aggregate gets tricky very quickly.

There are a couple of things that we can do, none of them are easy as the Khan academy samples you've seen so far, because the crux of the matter is that we need to (1) determine who the winner was by getting the top score and (2) determine the other fields that came from the "winning" record.

(Khanacademy was very careful with what they showed you in order to not get you to this place too fast because of how things can get hard in a hurry).

Suppose that we do this via a join (here is the SQL Fiddle http://sqlfiddle.com/#!18/5b8f4/10 ):

-- now for each of the winning game records (showing 1 record per player), now we can just tally them up
SELECT player_name, count(player_name) as GamesWon
FROM
(
  -- here, we join our card_games table to the winning scores to get just those lines via an inner join
  SELECT played.date_played, played.game_name, played.player_name, played.score, winScores.topscore
  FROM card_games as played
  INNER JOIN 
  (
    -- for each game, find out what the top score was
    SELECT date_played, game_name, MAX(score) as topscore
    FROM card_games
    GROUP BY date_played, game_name
  ) as winScores on winScores.date_played=played.date_played and winScores.game_name=played.game_name and winScores.topscore=played.score
) as gamesWon
GROUP BY player_name

There's also a more complicated thing that we can do using row_count and partition by, like this (sql fiddle http://sqlfiddle.com/#!18/5b8f4/14 ):

-- now for each of the winning game records (showing 1 record per player), now we can just tally them up
SELECT player_name, count(player_name) as GamesWon
FROM
(

  -- let's use the row_number to assign an int, 1,2,3,4, ... etc according to the highest score down
  -- and we will do this (partition by) for each unique date_played-to-game_name combination
  SELECT 
    card_games.*,
    ROW_NUMBER() OVER(PARTITION BY date_played, game_name ORDER BY score DESC) AS [Score Ranking]
  FROM card_games

) as gamesWon
WHERE [Score Ranking]=1
GROUP BY player_name