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

user_15715215 0
Viewed 152

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
window-functions sql-server-2016 sql-server sql
3 Answers

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.

user_104988280

Why not just use aggregation?

select min(row_number), id, min(ordertype), 
       sum(value), max(referencecode)
from a
group by id;
user_11440350

I have tried to write query for you

SELECT 
  id,
  (SELECT TOP 1 ot FROM test WHERE id=t.id ORDER BY ot) ot,
  ROW_NUMBER() OVER (PARTITION BY id ORDER BY id ) as row_num,
  SUM(val) as Value,
  MAX(ref_code) as ReferenceCode
FROM test as t
GROUP BY id;

See the working demo SQLFiddle

user_36554860
Related