Displaying calculated averages for all football matches from fixtures list

[DariusB]

Hello 

 

I have a database with matches for Championship and want to display a list of upcoming matches and average number of goals in matches for each of the teams from last 5 matches that they were involved in. 

E.g.

SELECT ko_time, home, away FROM matches_table WHERE ko_time = TODAY

will give me something like this.

15:00 Blackburn Rovers Charlton Athletic
15:00 Brentford Preston North End
15:00 Bristol City Reading
15:00 Huddersfield Town Bolton Wanderers
15:00 Hull City Queens Park Rangers

Now, I know how to calculate an average number of goals for one team from their last 5 matches e.g.

SELECT AVG(home_score + away_score) FROM matches_table WHERE
home = "Brentford" or away = "Brentford"
ORDER BY match_date DESC
LIMIT 5

This will give me average number of goals from 5 last matches of Brentford.

Now, I'm not sure how to combine these two queries and have the averages calculated for all teams and displayed like below:

15:00 Blackburn Rovers Charlton Athletic 2.3 2.4
15:00 Brentford Preston North End 1.4 2.3
15:00 Bristol City Reading 2.6 0.9
15:00 Huddersfield Town Bolton Wanderers 3.6 1
15:00 Hull City Queens Park Rangers 0.8 1.65

The table contains all matches - played and fixtures, which are updated once finished. 

Has anyone got any ideas? Thank you

Attached link to db

Database

[Barand]

try

SELECT ko_time
, team
, AVG(score)
FROM
    (
    SELECT score
    , @seq:=IF(team<>@prev,1,@seq+1) as seq
    , @prev:=team as team
    FROM
        (
        SELECT
           'H' as venue
          , match_date
          , home as team
          , home_score as score
        FROM championship
        WHERE match_date < CURDATE()
        UNION ALL
        SELECT
           'A' as venue
          , match_date
          , away as team
          , away_score as score
        FROM championship
        WHERE match_date < CURDATE()
        ORDER BY team, match_date DESC
        ) scores
            JOIN (SELECT @prev:='', @seq=0) init
    ) av
    INNER JOIN
    championship c ON av.team IN (c.home, c.away)
WHERE c.match_date = curdate() AND seq < 6
GROUP BY team;

[DariusB]

Thank you for taking time to help me. 

I tried your code and that returned: 
 

15:00 Blackburn 0.0000
15:00 Bolton 0.0000
15:00 Brentford 0.0000
15:00 Brighton0.0000 
15:00 Bristol City 0.0000
etc.

Did you get anything different?

Edited September 20, 2015 by DariusB

[Barand]

I had to change CURDATE() in the final WHERE clause (no Sunday matches)

mysql> SELECT ko_time
    -> , team
    -> , AVG(score)
    -> FROM
    ->     (
    ->     SELECT score
    ->     , @seq:=IF(team<>@prev,1,@seq+1) as seq
    ->     , @prev:=team as team
    ->     FROM
    ->         (
    ->         SELECT
    ->            'H' as venue
    ->           , match_date
    ->           , home as team
    ->           , home_score as score
    ->         FROM championship
    ->         WHERE match_date < CURDATE()
    ->         UNION ALL
    ->         SELECT
    ->            'A' as venue
    ->           , match_date
    ->           , away as team
    ->           , away_score as score
    ->         FROM championship
    ->         WHERE match_date < CURDATE()
    ->         ORDER BY team, match_date DESC
    ->         ) scores
    ->             JOIN (SELECT @prev:='', @seq=0) init
    ->     ) av
    ->     INNER JOIN
    ->     championship c ON av.team IN (c.home, c.away)
    -> WHERE c.match_date = '2015-09-19' AND seq < 6
    -> GROUP BY team;
+---------+--------------------+------------+
| ko_time | team               | AVG(score) |
+---------+--------------------+------------+
| 15:00   | Blackburn          |     0.6000 |
| 15:00   | Bolton             |     0.6000 |
| 15:00   | Brentford          |     0.6000 |
| 15:00   | Brighton           |     1.4000 |
| 15:00   | Bristol City       |     1.0000 |
| 15:00   | Cardiff            |     1.2000 |
| 15:00   | Charlton           |     1.0000 |
| 15:00   | Fulham             |     1.4000 |
| 15:00   | Huddersfield       |     0.6000 |
| 15:00   | Hull City          |     1.0000 |
| 15:00   | Leeds              |     0.8000 |
| 12:30   | Middlesbrough      |     1.6000 |
| 15:00   | Milton Keynes Dons |     0.2000 |
| 12:30   | Nottingham         |     1.0000 |
| 15:00   | Preston            |     0.6000 |
| 15:00   | QPR                |     1.6000 |
| 15:00   | Reading            |     1.6000 |
| 15:00   | Rotherham          |     1.0000 |
| 15:00   | Sheffield Wed      |     0.6000 |
| 15:00   | Wolves             |     1.0000 |
+---------+--------------------+------------+
20 rows in set (0.02 sec)

[DariusB]

That's superb. Thank you. 

 

Is there any way to display the table like the fixtures:

15:00 Blackburn Rovers Charlton Athletic 2.3 2.4
15:00 Brentford Preston North End 1.4 2.3
15:00 Bristol City Reading 2.6 0.9
15:00 Huddersfield Town Bolton Wanderers 3.6 1
15:00 Hull City Queens Park Rangers 0.8 1.65

Instead of the list of teams with averages? 

Thank you again. 

[DariusB]

Also, have another question: 

 

I would like to calculate average number of total goals e.g. ( average of home_score + away score) in H2H matches of given teams from last 5 H2H matches.

 

I can do that for single matches e.g.

SELECT AVG(home_score + away_score)
FROM championship WHERE
home ="Blackburn Rovers" AND away = "Charlton Athletic" or home = "Charlton Athletic" and away = "Blackburn Rovers"
ORDER BY match_date DESC
Limit 5 

Is there any way to have it done for all fixtures at once and displaying a table with fixtures and AVG(home_score + away_score) next to them and number of H2H matches of those teams?

e.g. 

15:00 Blackburn Rovers Charlton Athletic 2.3  3
15:00 Brentford Preston North End 0  0
15:00 Bristol City Reading  1.3  3
15:00 Huddersfield Town Bolton Wanderers 2  1  
15:00 Hull City Queens Park Rangers 2.45  4 

Thank you

[Barand]

The only way I could think of to get the two averages is to join twice to the subquery containing the team average calculation

SELECT c.ko_time
	, c.home
	, c.away
	, ROUND(h.average,1) as home_av
	, ROUND(a.average,1) as away_av
	FROM championship c
	INNER JOIN 
		(
		SELECT team
		, AVG(score) as average
		FROM
			(
			SELECT score
			, venue
			, @seq:=IF(team<>@prev,1,@seq+1) as seq
			, @prev:=team as team
			FROM
				(
				SELECT
				   'H' as venue
				  , match_date
				  , home as team
				  , home_score as score
				FROM championship
				WHERE match_date < CURDATE()
				UNION ALL
				SELECT
				   'A' as venue
				  , match_date
				  , away as team
				  , away_score as score
				FROM championship
				WHERE match_date < CURDATE()
				ORDER BY team, match_date DESC
				) scores
					JOIN (SELECT @prev:='', @seq=0) init
			) av
		WHERE seq < 6
		GROUP BY team 
		) h ON h.team = c.home
	INNER JOIN 
	(
	SELECT team
	, AVG(score) as average
	FROM
		(
		SELECT score
		, venue
		, @seq:=IF(team<>@prev,1,@seq+1) as seq
		, @prev:=team as team
		FROM
			(
			SELECT
			   'H' as venue
			  , match_date
			  , home as team
			  , home_score as score
			FROM championship
			WHERE match_date < CURDATE()
			UNION ALL
			SELECT
			   'A' as venue
			  , match_date
			  , away as team
			  , away_score as score
			FROM championship
			WHERE match_date < CURDATE()
			ORDER BY team, match_date DESC
			) scores
				JOIN (SELECT @prev:='', @seq=0) init
		) av
	WHERE seq < 6
	GROUP BY team
	) a ON a.team = c.away
	WHERE c.match_date = '2015-09-19'
	ORDER BY h.team;
+---------+--------------------+---------------+---------+---------+
| ko_time | home               | away          | home_av | away_av |
+---------+--------------------+---------------+---------+---------+
| 15:00   | Blackburn          | Charlton      |     0.6 |     1.0 |
| 15:00   | Brentford          | Preston       |     0.6 |     0.6 |
| 15:00   | Bristol City       | Reading       |     1.0 |     1.6 |
| 15:00   | Huddersfield       | Bolton        |     0.6 |     0.6 |
| 15:00   | Hull City          | QPR           |     1.0 |     1.6 |
| 15:00   | Milton Keynes Dons | Leeds         |     0.2 |     0.8 |
| 12:30   | Nottingham         | Middlesbrough |     1.0 |     1.6 |
| 15:00   | Rotherham          | Cardiff       |     1.0 |     1.2 |
| 15:00   | Sheffield Wed      | Fulham        |     0.6 |     1.4 |
| 15:00   | Wolves             | Brighton      |     1.0 |     1.4 |
+---------+--------------------+---------------+---------+---------+
10 rows in set (0.07 sec)

The second question you asked will require you to use a similar technique (though I think only a single subquery join will be necessary in that one)

[DariusB]

That's some very impressive stuff right there. Thank you very much for all your help and time

[Barand]

BTW, your query

 

SELECT AVG(home_score + away_score)
FROM championship WHERE
home ="Blackburn Rovers" AND away = "Charlton Athletic" or home = "Charlton Athletic" and away = "Blackburn Rovers"
ORDER BY match_date DESC
Limit 5

 

is wrong.

 

LIMIT limits the number of rows output by the query, it doesn't limit the input to the latest 5. That's why I had to use the subqueries with @seq to get the latest 5.

[Barand]

This should do it

SELECT
    ko_time 
  ,    c.home
  , c.away
  , av_goals
  , meets
FROM
    championship c 
LEFT JOIN 
    (
    SELECT teams
    , AVG(home_score + away_score) as av_goals
    , COUNT(*) as meets
    FROM 
        (
        SELECT home_score
        , away_score
        , @seq := IF(@prev <> CONCAT(LEAST(home,away),GREATEST(home,away)),1,@seq+1) as seq
        , @prev := CONCAT(LEAST(home,away),GREATEST(home,away)) as teams
        FROM
        championship
            JOIN (SELECT @prev:='', @seq:=0) init
        WHERE match_date < '2015-09-19'
        ORDER BY CONCAT(LEAST(home,away),GREATEST(home,away)), match_date DESC
        ) last5
    WHERE seq < 6
    GROUP BY teams
    ) H ON teams = CONCAT(LEAST(c.home,c.away),GREATEST(c.home,c.away))
WHERE match_date = '2015-09-19'
ORDER BY c.home, c.away;
+---------+--------------------+---------------+----------+-------+
| ko_time | home               | away          | av_goals | meets |
+---------+--------------------+---------------+----------+-------+
| 15:00   | Blackburn          | Charlton      |   2.8000 |     5 |
| 15:00   | Brentford          | Preston       |     NULL |  NULL |
| 15:00   | Bristol City       | Reading       |     NULL |  NULL |
| 15:00   | Huddersfield       | Bolton        |   1.4000 |     5 |
| 15:00   | Hull City          | QPR           |     NULL |  NULL |
| 15:00   | Milton Keynes Dons | Leeds         |     NULL |  NULL |
| 12:30   | Nottingham         | Middlesbrough |   2.6000 |     5 |
| 15:00   | Rotherham          | Cardiff       |   4.0000 |     1 |
| 15:00   | Sheffield Wed      | Fulham        |   3.0000 |     2 |
| 15:00   | Wolves             | Brighton      |   3.0000 |     4 |
+---------+--------------------+---------------+----------+-------+
10 rows in set (0.08 sec)

[DariusB]

Can't thank you enough. That's superb. Best regards.