How to sum each unique last value from table

Question

I want to SUM the total amount of KG of each unique last value in a table.

The table i currently have looks like this

|----|-----------|----------|----------|----------|---------------|
| id | productId | amountKg | amountSt | location | datetimeStamp |
|----|-----------|----------|----------|----------|---------------|
| 12 | 19        | 201      | 0        | loc1     | 2019-12-21    |
| 13 | 19        | 35       | 0        | loc2     | 2019-12-22    |  <---
| 14 | 19        | 400      | 0        | loc1     | 2019-12-23    |  <---
|----|-----------|----------|----------|----------|---------------|

And I have this SQL query

SELECT SUM(amountKg) FROM (SELECT amountKg FROM storage GROUP BY location ORDER BY id ASC) t;

Whe I run this query I get the result 236 The result I want is 435, the sum of the 14 and 13 (The arrows in the table)

Any help is appreciated

I'm not sure about the semantics of selecting a non-aggregated value in an aggregation query, but if you `ORDER BY id ASC` in the inner query, I'd expect it to return the value for the _lowest_ id (just take the first value, if they are ordered ascending, that is the lowest one). Have you tried ordering `DESC`? — He3lixxx, Dec 21 '19 at 17:36
Very similar: https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql — He3lixxx, Dec 21 '19 at 17:39

score 1 · Accepted Answer · answered Dec 21 '19 at 17:47

Find first the ID's that have the latest row for each location and then sum those together:

select sum(s.amountKg)
from storage s
  join ( 
    select location, max(id) as id
    from storage
    group by location
  ) as latest on latest.id=s.id

See DB-Fiddle

How to sum each unique last value from table

1 Answers1