MS SQL - SUM column values for distinct values in other columns-CodePudding

let's assume I have a table with employee production

id	work_order	product	qty	qty_to_sum
1	200	ABC	25
2	200	ABC	25

i would like to see this result:

id	work_order	product	qty	qty_to_sum
1	200	ABC	25	25
2	200	ABC	25	0

I want to place a value of qty in qty_to_sum column based on distinct work_order and product columns combination, so when I get the first distinct result I place a value in qty_to_sum and each row with the same work_order and product will get 0. I tried to use row_number but I didn't get what I needed.

CodePudding user response：

Use SUM with a window OVER, like :

SUM(qty) OVER(PARTITION BY work_order, product ORDER BY id DESC)

CodePudding user response：

You can try to use ROW_NUMBER window function then use CASE WHEN to judgment the first per work_order,product will show the value, others will display 0

Query 1:

SELECT *,
       (CASE WHEN ROW_NUMBER() OVER(PARTITION BY work_order,product  ORDER BY id)  = 1 THEN qty ELSE 0 END) 'qty_to_sum'
FROM T

Results:

| id | work_order | product | qty | qty_to_sum |
|----|------------|---------|-----|------------|
|  1 |        200 |     ABC |  25 |         25 |
|  2 |        200 |     ABC |  25 |          0 |