Returning Minimum Rows For Each ID SQL

Question

I have a very long list that looks roughlt like the below (column names and data anonymised)

ID | ID2 | Value
1  |  5  |  900
1  |  7  |  400
2  |  2  |  100
2  |  8  |  800
2  |  2  |  200
3  |  4  |  100
3  |  5  |  300
4  |  8  |  750
4  |  5  |  900

And what I am after is the ability to make 1 row per column "ID", which has the sum of the values of the lowest ID2.

As an example, the above table would turn into:

ID | ID2 | Value
1  |  5  |  900
2  |  2  |  300
3  |  4  |  100
4  |  5  |  900

I have tried several script variations to this with no success. This is my current cleaned up code:

    SELECT res.id
       , min(id2) as rdk
       ,sum(value)

  FROM x as res

    left join y as clai
    on res.id = clai.id

    left join z as cal
    on clai.col99 = cal.col99

    group by
       res.id

However the sum is adding up everything in value for the "ID", not just the lowest "ID2"?

Any help appreciated!

Answer 1

Try this out:

select a.*,b.value from
(select id,min(id2) as id2 
from have
group by id) a
left join
(select id,id2,sum(value) as value
from have
group by id,id2) b
on a.id=b.id and a.id2=b.id2;

My Output:

id  |id2 |value
1   |5   |900
2   |2   |300
3   |4   |100
4   |5   |900

Please let me know in case of any queries.