SQL Server Management Studio - Calculating Correct Total

Question

I have a column in a SQL Server table with values like this:

ID      Value  Item#
-------------------
101     10     Apples
102     20     Apples
102-01  22     Apples
102-02  23     Apples
102-03  21     Apples
103     20     Apples
103-01  15     Apples 

How to write a query to get correct total for values?

For the given example the values should only be taken for : 101, 102-03, 103-01.

The correct sum should be 46.

I tried using like and contains keywords in where clause. It did not help. Group by and Max messes up the total.

And just using sum(values) gives total of everything which is wrong

Answer 1

As I understand your question, you want to group records by the prefix of their id (the first 3 characters), and sum the value of the keep the record with the highest suffix per group (if there is more than one record).

You can use row_number(), then filtering and a sum:

select sum(value) total_value 
from (
    select 
        t.*,
        row_number() over(partition by left(id, 3) order by id desc) rn
    from mytable t
) t
where rn = 1