Sql Soccer Points Table Last 5 Matches

I have a MSSQL query for point table of all matches of this season. The database consists of one table and table column name is; Div (League name), Date, HomeTeam, AwayTeam, FTHG(

Solution 1:

I think this would work.

with table_a as (
    select Div, date, hometeam team, fthg, ftag, hthg, htag fromMatchesunionallselect Div, date, awayteam team, ftag, fthg, htag, hthg fromMatches 
    ) 
,table_b as (
select*from (
    select a.*
    ,row_number() over (partitionby a.team orderby a.date desc) as row_num
    from table_a a)
where row_num <=5)

select 
    team, 
    count(*) MP, 
    count(casewhen fthg > ftag then1end) W, 
    count(casewhen fthg = ftag then1end) D, 
    count(casewhen fthg < ftag then1end) L,
    sum(fthg) GF,
    sum(ftag) GA,
    sum(fthg) -sum(ftag) GD,
    sum(casewhen fthg > ftag then3else0end+casewhen fthg = ftag then1else0end) Pts
    from table_b
    where div='E0'groupby team
orderby Pts desc

Solution 2:

Since my edit to Jeremy Real's answer was rejected, I'm posting my own, with credit to @JeremyReal (also upvoted).

with table_a as (
    select Div, date, hometeam team, fthg, ftag, hthg, htag fromMatchesunionallselect Div, date, awayteam team, ftag, fthg, htag, hthg fromMatches 
    ) 
,table_b as (
select*from (
    select a.*
    ,row_number() over (partitionby a.Div, a.team orderby a.date desc) as row_num
    from table_a a) x
where row_num <=5)

select 
    Div,
    team, 
    count(*) MP, 
    count(casewhen fthg > ftag then1end) W, 
    count(casewhen fthg = ftag then1end) D, 
    count(casewhen fthg < ftag then1end) L,
    sum(fthg) GF,
    sum(ftag) GA,
    sum(fthg) -sum(ftag) GD,
    sum(casewhen fthg > ftag then3else0end+casewhen fthg = ftag then1else0end) Pts
    from table_b
    where div='E0'--remove this line to show all divisions.groupby Div, team
orderby Div, Pts desc

Compared with Jeremy's answer, this adds an alias (x) to the subquery for the table_b CTE. It also adds Div to the partitioning in that subquery and to the group by in main query.

My thought with including Div in the main query is that there could be teams with the same name operating in different competitions. Or even that the original data might include "cup" competitions alongside league events, and that the two should not be muddled. Depending on your data structures, you could even have multiple seasons of data in your table, which would also need to be handled.