Modify MySQL query to produce to right data-CodePudding

I am trying to produce a data from a football table.

The table can be seen below and the data that is needed is highlighted. Table data

The data that needs to be produced can be seen below. Required data

Now I have been at it for few days and I have managed to get somewhere, even though I am pretty far from the desired output. Here is the query I currently have.

SET @rownum := 0;
SELECT (@rownum := @rownum   1) as matchNo,
(SELECT
 CASE 
    WHEN home_score > away_score AND home_team_name ='Barcelona' THEN 3
    WHEN home_score < away_score AND away_team_name = 'Barcelona' THEN 3
    WHEN home_score > away_score AND away_team_name = 'Barcelona' THEN 0
    WHEN home_score < away_score AND home_team_name = 'Barcelona' THEN 0
    WHEN home_score = away_score AND home_team_name = 'Barcelona' THEN 1
    WHEN home_score = away_score AND away_team_name = 'Barcelona' THEN 1
    END) AS outcome,
(SELECT SUM(outcome) / 3) AS winrate
FROM matches
ORDER BY matches.match_id

The result I am getting is below

Produced Data

What I am trying to do is to move winrate as a second field and outcome as the last field and obviously get the correct winrate. Thank you all in advance.

CodePudding user response：

Here is the dataset I used (for the next times, please use text instead of pictures, which cannot be copy/pasted):

CREATE TABLE matches ( 
    match_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    home_team_name VARCHAR(30) NOT NULL,
    away_team_name VARCHAR(30) NOT NULL,
    home_score INT NOT NULL,
    away_score INT NOT NULL
); 

INSERT INTO matches (match_id, home_team_name, away_team_name, home_score, away_score) VALUES
(123, "Barcelona", "Paris", 3, 0),
(124, "Barcelona", "Milan", 2, 2),
(125, "Madrid", "Paris", 1, 1),
(126, "Milan", "Madrid", 1, 2),
(127, "Barcelona", "Madrid", 2, 0),
(128, "Milan", "Paris", 3, 4),
(201, "Paris", "Barcelona", 0, 0),
(202, "Paris", "Milan", 1, 2),
(203, "Paris", "Madrid", 2, 3),
(204, "Madrid", "Barcelona", 2, 3),
(205, "Madrid", "Milan", 3, 1),
(206, "Milan", "Barcelona", 2, 0);

You need a little bit more calculation to get the win rate, by using more counters like you did. This request will calculate the total and average of victories and points:

SELECT
    @rownum := @rownum   1 as matchNo,
    @rowvictory := (SELECT
     CASE 
        WHEN home_score > away_score AND home_team_name ='Barcelona' THEN 1
        WHEN home_score < away_score AND away_team_name = 'Barcelona' THEN 1
        ELSE 0
        END) AS victory,
    @rowpoints := (SELECT
     CASE 
        WHEN home_score > away_score AND home_team_name ='Barcelona' THEN 3
        WHEN home_score < away_score AND away_team_name = 'Barcelona' THEN 3
        WHEN home_score > away_score AND away_team_name = 'Barcelona' THEN 0
        WHEN home_score < away_score AND home_team_name = 'Barcelona' THEN 0
        WHEN home_score = away_score AND home_team_name = 'Barcelona' THEN 1
        WHEN home_score = away_score AND away_team_name = 'Barcelona' THEN 1
        END) AS points,
    (SELECT @points := @points   @rowpoints) as total_points,
    (SELECT @points / @rownum) AS average_points,
    (SELECT @victories := @victories   @rowvictory) as total_victories,
    (SELECT @victories / @rownum) AS average_victories
FROM matches
JOIN (SELECT @rownum := 0) AS rownum
JOIN (SELECT @points := 0) AS points
JOIN (SELECT @victories := 0) AS victories
WHERE home_team_name = 'Barcelona'
OR away_team_name = 'Barcelona'

-- matchNo  victory points total_points average_points total_victories average_victories
-- 1        1       3      3            3              1               1
-- 2        0       1      4            2              1               0.5
-- 3        1       3      7            2.33           2               0.66
-- 4        0       1      8            2              2               0.5
-- 5        1       3      11           2.2            3               0.6
-- 6        0       0      11           1.83           3               0.5

Now you can simply wrap it in another query, to get the columns you want, in the order you want:

SELECT matchNo, average_victories AS winrate, points AS outcome
FROM (
    SELECT
        @rownum := @rownum   1 as matchNo,
        @rowvictory := (SELECT
         CASE 
            WHEN home_score > away_score AND home_team_name ='Barcelona' THEN 1
            WHEN home_score < away_score AND away_team_name = 'Barcelona' THEN 1
            ELSE 0
            END) AS victory,
        @rowpoints := (SELECT
         CASE 
            WHEN home_score > away_score AND home_team_name ='Barcelona' THEN 3
            WHEN home_score < away_score AND away_team_name = 'Barcelona' THEN 3
            WHEN home_score > away_score AND away_team_name = 'Barcelona' THEN 0
            WHEN home_score < away_score AND home_team_name = 'Barcelona' THEN 0
            WHEN home_score = away_score AND home_team_name = 'Barcelona' THEN 1
            WHEN home_score = away_score AND away_team_name = 'Barcelona' THEN 1
            END) AS points,
        (SELECT @points := @points   @rowpoints) as total_points,
        (SELECT @points / @rownum) AS average_points,
        (SELECT @victories := @victories   @rowvictory) as total_victories,
        (SELECT @victories / @rownum) AS average_victories
    FROM matches
    JOIN (SELECT @rownum := 0) AS rownum
    JOIN (SELECT @points := 0) AS points
    JOIN (SELECT @victories := 0) AS victories
    WHERE home_team_name = 'Barcelona'
    OR away_team_name = 'Barcelona'
) AS t

-- matchNo winrate  outcome
-- 1       1        3
-- 2       0.5      1
-- 3       0.66     3
-- 4       0.5      1
-- 5       0.6      3
-- 6       0.5      0