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

Question

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.

I tried to use row_number Show your "attempt".

SMor
– SMor

2022-03-02 12:31:58 +00:00
Commented Mar 2, 2022 at 12:31 — SMor
– SMor, Commented Mar 2, 2022 at 12:31

D-Shih · Accepted Answer · 2022-03-02 12:26:54Z

1

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 |

answered Mar 2, 2022 at 12:26

D-Shih

46.5k6 gold badges38 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SQLpro · Accepted Answer · 2022-03-02 12:26:45Z

1

Use SUM with a window OVER, like :

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

answered Mar 2, 2022 at 12:26

SQLpro

5,3121 gold badge9 silver badges19 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related