Trying to add Value and Maximum value by Using Row Number Function

Question

Table A

Row Number     ID    orderType    value     Reference Code
1              1      A            5          2
2              1      A.1          2          4
1              2      A            6          5
2              2      A.1          2          1

I am trying to get this result Table B

Row Number     ID    orderType    value     Reference Code
1              1      A            7          4
1              2      A            8          5

I am using SQL Server 2016 and trying to achieve a result in Like a TABLE B, where I can add Value and also get maximum Reference Code.

I am using Row_Number and Sum together but could not get desired result.

My query is

SELECT 
  ID,
  Ordertype ,
  ROW_NUMBER() over (Partition by Id order by OrderType ) as Row Number,
  sum([Value]) over( partition by id) as Value,
  Max(Reference Code) as Reference Code

From Table A

where row number = 1

Answer 1

Use window functions: SUM() for the total value, MIN() for orderType and MAX() for Reference_Code:

SELECT DISTINCT ID,
       MIN(orderType) OVER (PARTITION BY ID) orderType,
       SUM(value) OVER (PARTITION BY ID) value,
       MAX(Reference_Code) OVER (PARTITION BY ID) Reference_Code
FROM TableA

See the demo.