CODEWITHSUNDEEP
mysql
ShaneOH
ShaneOH

Reputation: 1557

MySQL get the SUM of a column ONLY from the latest date

I have a table like this:

---------------------------------------
| id | name     | points |   date     |
---------------------------------------
| 1  | ana      | 1      | 2014-12-10 |
| 2  | tom      | 3      | 2015-12-09 |
| 3  | jim      | 1      | 2013-12-02 |
| 4  | ana      | 9      | 2014-12-10 |
| 5  | tom      | 3      | 2015-12-09 |
| 6  | jim      | 1      | 2016-12-08 |
| 7  | jim      | 5      | 2016-12-08 |
| 8  | ana      | 2      | 2016-12-08 |
| 9  | ana      | 1      | 2016-12-08 |
| 10 | tom      | 2      | 2013-12-07 |
-------------------------------------

In this table, I have a group of names. These names may have duplicate entries on the same date with different points. They also have duplicate entries with different dates. The dates are all different, there is no pattern to them.

My goal is to return the following:

name, SUM(points) WHERE MAX(date) for the name group

So basically my current query is:

SELECT name, SUM(points) FROM table GROUP BY name;

However, I need this to only count point entries in the name group from the latest date for that name group, as opposed to summing all the points from all dates.

How can I do this?

Upvotes: 1

Views: 2896

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270627

To aggregate the latest date per name, you can join against a subquery producing the MAX(date), joining on both the name and the date from the outer table and the subquery. This allows you to SUM() for that group.

SELECT
  t.name,
  SUM(points) AS total_points,
  max_date
FROM
  `table` t
  JOIN (
    -- Subquery produces latest date per name
    SELECT 
      name,
      MAX(date) AS max_date
    FROM `table`
    GROUP BY name
  ) name_dates
    -- Join table to subquery on both name and the aggregate date
    ON t.name = name_dates.name AND t.date = max_date
-- Group for the outer SUM()
GROUP BY t.name, max_date

This produces the result:

+------+--------------+---------------------+
| name | total_points | max_date            |
+------+--------------+---------------------+
| ana  |            3 | 2016-12-08 00:00:00 |
| jim  |            6 | 2016-12-08 00:00:00 |
| tom  |            6 | 2015-12-09 00:00:00 |
+------+--------------+---------------------+

Here it is in action. http://sqlfiddle.com/#!9/dece38/1 (sqlfiddle is malfunctioning right now, may need to paste the query back in to reexecute)

Upvotes: 2

Related Questions