SQL-DateAdded Function and Sum issue

Question

I have the following query:

SELECT 
    p.name as Name, SUM(r.attempts) as Attempts, SUM(r.gains) as Positive,       
    SUM(r.losses) as Negative, SUM(r.yards) as Net, SUM(r.touchdowns) as TD,
    SUM(r.longest) as Long, year(g.date_) as Season 
FROM 
    Rush AS r 
INNER JOIN                
    PlayerGame AS pg ON r.playerGame_Id = pg.playerGame_Id 
INNER JOIN 
    Players AS p ON p.player_Id = pg.player_Id
INNER JOIN 
    Games as g ON g.game_Id = pg.game_Id 
WHERE
    g.date_ LIKE CASE 
                    WHEN month(g.date_) = 1 
                       THEN DATEADD(year, -1, g.date_)
                    ELSE g.date_ 
                 END
GROUP BY
    year(date_), name
ORDER BY 
    Season DESC, Attempts DESC, Positive, Negative, Net, TD, Long

I am attempting to get the summation of all the stats from each season. I am trying to add all the extra games played in January for a full season. When I run this query, the seasons are separate with the January dates. However, for all the games played in January, it does not add them to the sum of all the attempts, yards, etc. If anyone could help or if you need extra context let me know. Thanks

Answer 1

You should use a year with a substracted month this for mysql

SELECT p.name as Name, SUM(r.attempts) as Attempts, SUM(r.gains) as Positive,       
SUM(r.losses) as Negative, SUM(r.yards) as Net, SUM(r.touchdowns) as TD,
SUM(r.longest) as Long, year(DATE_SUB(date_,INTERVAL 1  MONTH)) as Season 
FROM Rush AS r INNER JOIN                
PlayerGame AS pg ON r.playerGame_Id = pg.playerGame_Id 
INNER JOIN Players AS p ON p.player_Id = pg.player_Id
INNER JOIN Games as g ON g.game_Id = pg.game_Id 

Group by year(DATE_SUB(date_,INTERVAL 1  MONTH)), name
Order by Season DESC, Attempts DESC, Positive, Negative, Net, TD, Long

This for sql-server

SELECT p.name as Name, SUM(r.attempts) as Attempts, SUM(r.gains) as Positive,       
SUM(r.losses) as Negative, SUM(r.yards) as Net, SUM(r.touchdowns) as TD,
SUM(r.longest) as Long, year(DATEADD(month, -1, date_)) as Season 
FROM Rush AS r INNER JOIN                
PlayerGame AS pg ON r.playerGame_Id = pg.playerGame_Id 
INNER JOIN Players AS p ON p.player_Id = pg.player_Id
INNER JOIN Games as g ON g.game_Id = pg.game_Id 

Group by year(DATEADD(month, -1, date_)), name
Order by Season DESC, Attempts DESC, Positive, Negative, Net, TD, Long