select row with the same value with limit for each of them

Question

Hi I got problem trying to generate football table based on last 4 matches results this is the code I'm using to generate table based on all results and it works fine:

select 
    team, 
    count(*) played, 
    count(case when goalsfor > goalsagainst then 1 end) wins, 
    count(case when goalsagainst> goalsfor then 1 end) lost, 
    count(case when goalsfor = goalsagainst then 1 end) draws, 
    sum(goalsfor) goalsfor, 
    sum(goalsagainst) goalsagainst, 
    sum(goalsfor) - sum(goalsagainst) goal_diff,
    sum(
          case when goalsfor > goalsagainst then 3 else 0 end 
        + case when goalsfor = goalsagainst then 1 else 0 end
    ) score 
from (
    select hometeam team, goalsfor, goalsagainst from scores 
  union all
    select awayteam, goalsagainst, goalsfor from scores
) a 
group by team
order by score desc, goal_diff desc;

this is table

 id  | hometeam | awaytem | goalsfor | goalsagainst | time | data
 1   | team a   | team b  |   3      |    2         | 13:00| 2016-04-21
 2   | team c   | team b  |   4      |    1         | 13:00| 2016-04-19

I have no idea how to select only 4 last games for all teams the most recent one I tried that way:

 (
    select hometeam team, goalsfor, goalsagainst from scores 
  union all
    select awayteam, goalsagainst, goalsfor from scores where data>2016-03-21
)

But some of the teams played only one game for that period others 4

Answer 1

To expand on @jarlh's comment, presumably you want last 4 games regardless of whether the games were home or away, so you will want to "limit" your results up a level to [a]:

from (
    select hometeam team, goalsfor, goalsagainst, data from scores 
  union all
    select awayteam, goalsagainst, goalsfor, data from scores
) a order by data desc limit 4

Answer 2

that's the output of the whole code

+---+-----------------------------------------------------------------------+
|   |                                   A                                   |
+---+-----------------------------------------------------------------------+
| 1 | team    played wins lost draws goalsfor goalsagainst goal_diff score  |
| 2 | Chelsea  708   272    272  164     976          976       0     980   |
+---+-----------------------------------------------------------------------+

that's the PARTIAL output of the middle part you asked

+-------------------+---+---+----------+---+
| Chelsea           | 3 | 0 | 5/8/2017 | 1 |
| Middlesbrough     | 0 | 3 | 5/8/2017 | 1 |
| Liverpool         | 0 | 0 | 5/7/2017 | 1 |
| Arsenal           | 2 | 0 | 5/7/2017 | 1 |
| Southampton       | 0 | 0 | 5/7/2017 | 1 |
| Manchester United | 0 | 2 | 5/7/2017 | 1 |
| Bournemouth       | 2 | 2 | 5/6/2017 | 1 |
| Sunderland        | 2 | 0 | 5/6/2017 | 1 |
| Swansea           | 1 | 0 | 5/6/2017 | 1 |
| West Brom         | 2 | 2 | 5/6/2017 | 1 |
| Stoke City        | 2 | 2 | 5/6/2017 | 1 |
| Manchester City   | 5 | 0 | 5/6/2017 | 1 |
| Everton           | 0 | 1 | 5/6/2017 | 1 |
| Leicester         | 3 | 0 | 5/6/2017 | 1 |
| Hull City         | 0 | 2 | 5/6/2017 | 1 |
| Crystal Palace    | 0 | 5 | 5/6/2017 | 1 |
| Burnley           | 2 | 2 | 5/6/2017 | 1 |
| Watford           | 0 | 3 | 5/6/2017 | 1 |
| West Ham          | 1 | 0 | 5/5/2017 | 1 |
| Tottenham         | 0 | 1 | 5/5/2017 | 1 |
| Watford           | 0 | 1 | 5/1/2017 | 1 |
| Liverpool         | 1 | 0 | 5/1/2017 | 1 |
+-------------------+---+---+----------+---+

it says there is 708 rows in total

Answer 3

Your FROM subquery is almost there, but you need include date.

from (
    select hometeam team, goalsfor, goalsagainst, date from scores 
     union all
    select awayteam team, goalsagainst, goalsfor, date from scores
) a 

Now you need to select the last 4 games of each team, for that you need use variables.

SELECT *
FROM (
    SELECT A.*, 
           @rn := IF( @team = team,
                      @rn + 1, 
                      IF( @team := team, 1, 1)
                    ) as rn
    FROM (
        select hometeam team, goalsfor, goalsagainst, date from scores 
         union all
        select awayteam team, goalsagainst, goalsfor, date from scores
    ) a
    CROSS JOIN ( SELECT @rn := 0, @team := '' ) as var
    ORDER BY team, date DESC 
   ) T
WHERE T.rn <= 4   
ORDER BY team, rn  

Now you can do your aggregation over that result:

SELECT team, count(*), .....
FROM ( /* previous query */ )  as F
GROUP BY team