CODEWITHSUNDEEP
sql-serversumcasedateadd
Matt Fazzini
Matt Fazzini

Reputation: 151

SQL-DateAdded Function and Sum issue

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

Upvotes: 1

Views: 36

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

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

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89721

Create a table Seasons with columns Season, SeasonStart and SeasonEnd.

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, s.Season 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 
INNER JOIN Seasons AS s on g.date_ BETWEEN s.SeasonStart AND s.SeasonEnd
Group by s.Season, name
Order by s.Season DESC, Attempts DESC, Positive, Negative, Net, TD, Long

Then the need for special date logic goes away and is represented by the table.

Upvotes: 0

Related Questions