CODEWITHSUNDEEP
mysqlsqldatabaseselect
Juzaa
Juzaa

Reputation: 13

Getting SUM of latest entries with different column identifier

I have a table with following structure:

-----------------------------
|id|value|category|timestamp|
|1 |100  |1       |10       |
|2 |200  |1       |20       |
|3 |100  |2       |30       |
-----------------------------

How can I get the SUM of the LATEST values of different categories? So in this case I would like to get SUM of rows 2 and 3

Upvotes: 1

Views: 25

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

One method uses a correlated subquery:

select sum(t.value)
from t
where t.timestamp = (select max(t2.timestamp)
                     from t t2
                     where t2.category = t.category
                    );

With an index on (category, timestamp), this is likely the fastest method. But you can also use window functions:

select sum(t.value)
from (select t.*,
             row_number() over (partition by category order by timestamp desc) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions