CODEWITHSUNDEEP
sqlsql-servert-sqlsummax
Rooter
Rooter

Reputation: 383

How to get Max date and sum of its rows SQL

I have following table,

+------+-------------+----------+---------+
| id   | date        | amount   | amount2 |
+------+-------------+----------+---------+
|      |             |          | 500     |
| 1    | 1/1/2020    |  1000    |         |
+------+-------------+----------+---------+
|      |             |          | 100     |
| 1    | 1/3/2020    |  1558    |         |
+------+-------------+----------+---------+
|      |             |          | 200     |
| 1    | 1/3/2020    |   126    |         |
+------+-------------+----------+---------+
|      |             |          | 500     |
| 2    | 2/5/2020    |  4921    |         |
+------+-------------+----------+---------+
|      |             |          | 100     |
| 2    | 2/5/2020    |    15    |         |
+------+-------------+----------+---------+
|      |             |          | 140     |
| 2    | 1/1/2020    |  5951    |         |
+------+-------------+----------+---------+
|      |             |          | 10      |
| 2    | 1/2/2020    |  1588    |         |
+------+-------------+----------+---------+
|      |             |          | 56      |
| 2    | 1/3/2020    |  1568    |         |
+------+-------------+----------+---------+
|      |             |          | 45      |
| 2    | 1/4/2020    | 12558    |         |
+------+-------------+----------+---------+

I need to get each Id's max date and its amount and amount2 summations, how can I do this. according to above data, I need following output.

+------+-------------+----------+---------+
|      |             |          | 300     |
| 1    | 1/3/2020    |  1684    |         |
+------+-------------+----------+---------+
|      |             |          | 600     |
| 2    | 2/5/2020    |   4936   |         |
+------+-------------+----------+---------+

How can I do this.

Upvotes: 0

Views: 1301

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Aggregate and use MAX OVER to get the IDs' maximum dates:

select id, [date], sum_amount, sum_amount2 
from
(
  select
    id, [date], sum(amount) as sum_amount, sum(amount2) as sum_amount2,
    max([date]) over (partition by id) as max_date_for_id
  from mytable group by id, [date]
) aggregated
where [date] = max_date_for_id
order by id;

Upvotes: 3

Squirrel
Squirrel

Reputation: 24763

first is to use dense_rank() to find the row with latest date

dense_rank () over (partition by id order by [date] desc)

after that, just simply group by with sum() on the amount

select  id, [date], sum(amount), sum(amount2)
from
(
    select  *, 
            dr = dense_rank () over (partition by id order by [date] desc)
    from    your_table
) t
where   dr  = 1
group by id, [date]

Upvotes: 2

Related Questions