ScottPletcher (2/2/2016)
Give this query a try: I couldn't test it as I don't have data:
SELECT team,sum(ats) as W,sum(atsloss) as L,sum(O) as Ov, sum(U) as Un,
sum(case when line < 0 and ats = 1 then 1 else 0 end) as favW,
sum(case when line < 0 and atsloss = 1 then 1 else 0 end) as favL
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY team ORDER BY gmdate DESC) AS row_num
FROM teamLogs
) AS derived
WHERE row_num BETWEEN 1 AND 5
GROUP BY team
--ORDER BY team
Slight change to the code would give what's requested:
WINSlast5games
SELECT team,sum(ats) as W,
sum(CASE WHEN row_num BETWEEN 1 AND 5 THEN ats ELSE 0 END ) as WINSlast5games ,
...
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY team ORDER BY gmdate DESC) AS row_num
FROM teamLogs
) AS derived
GROUP BY team
_____________
Code for TallyGenerator