Group rows with Id from specific row

Question

I've been breaking my head trying to work this out, so some help here would be appreciated very much.

Basically I've got a database table, for example:

I'm trying to get the below result:

Id     | Name  | Total
------------------------
590954 | ABC   | 825.00

So I need the ID from the row with the largest Value for this group (i.e. ABC), the Name and the sum of Value (for this group - ABC).

There are many more rows with different names.

Thanks in advance.

Edit (adding some SQL to help out):

declare     @Test       table
    (       Id          int
    ,       Name        nvarchar(20)
    ,       Value       decimal(10,2)
    )

insert into @Test values (590954, 'ABC', 525)
insert into @Test values (592332, 'ABC', 300)
insert into @Test values (1, 'DEF', 100)
insert into @Test values (2, 'DEF', 250)

select * from @Test

The result I'm trying to get is these 2 rows:

Id     | Name  | Total
------------------------
590954 | ABC   | 825.00
2      | DEF   | 350.00

Please help me in writing the required query.

Answer 1

SQL DEMO

You can try the following query :

select t1.id, t1.name, t2.Total
from table1 t1
join(SELECT name, MAX(Value) as max_Value, SUM(Value) as Total
    FROM Table1 GROUP BY name) t2 
    on t1.name=t2.name and t1.Value=t2.max_Value

To understand the query : The derived table aliased as t2 which contains the fields name , max Value and sum of Value of each group. Then it is joined with the main table table1 with this condition t1.name=t2.name and t1.Value=t2.max_Value which confirms to select only the record which have the max Value of each group.