CODEWITHSUNDEEP
mysqlsql
DaveC1991
DaveC1991

Reputation: 1

How do you SUM values based on a MIN date?

Working on an exercise for school trying to calculate the number of points scored by a basketball player ONLY during their first game.

So if I have a table that reports lots of games (Separate rows for 1st and 2nd half) that looks like this:

Game Date   Player  Half    Points
1990-01-01  Mike    1        10
1990-01-01  Mike    2        10
1990-01-03  Mike    1        5
1990-01-03  Ben     2        8
1990-01-05  Kelly   1        4
1990-01-05  Kelly   2        4
1990-01-07  Kelly   1        10

And I want it to end up like this: 

Game Date   Player  Points
1990-01-01  Mike    20
1990-01-03  Ben     8
1990-01-05  Kelly   8

How would I do this?

I have been trying to use the code:

SELECT min(game_Date), player, sum(points);

But it keeps counting points for ALL games, not just points scored during the 1st game, of which there can be one record for the first half and one record for the second.

Upvotes: 0

Views: 1144

Answers (3)

Kiran Muralee
Kiran Muralee

Reputation: 2060

Sub query approach of getting the result is given below

  1. Filter the results only getting Player's first games.

Select min(Game_Date),Player from basketball group by Player;

  1. Use the results from first query to find each player's sum of scores in first game.

Select Game_Date, Player,sum(points) as first_play_points from basketball where (Game_date,Player) in (Select min(Game_Date),Player from basketball group by Player) group by Game_Date, Player;

Working fiddle can be found here

Upvotes: 0

podiluska
podiluska

Reputation: 51494

First you need to find the players' first games, like this

 select player, min(game_date) as firstGameDate
 from yourtable
 group by player

and then get the points in that game by joining to the table again

 select yourtable.player, firstgame.firstGameDate, sum(points) as firstGamePoints
 from yourtable
      inner join 
 (
 select player, min(game_date) as firstGameDate
 from yourtable
 group by player
 ) firstgame
      on yourtable.player = firstgame.player
      and yourtable.game_date = firstgame.firstgameDate
 group by yourtable.player, firstgame.firstgameDate

Some varieties of SQL allow you to use ranking functions which could eliminate the need to join to the table itself, but this will work in all varieties.

Upvotes: 2

Eric
Eric

Reputation: 3257

You have to use your logic. First you have to only grab the first game for each player (inner query). Then from there, you count the points

SELECT t.game_date, t.player, SUM(t.points)
FROM some_table t
JOIN (
    SELECT player, MIN(game_date) AS min_date
    FROM some_table
    GROUP BY player
) a ON a.plyer = t.player AND a.min_date = t.game_date
GROUP BY t.player, t.game_date

Upvotes: 1

Related Questions