Derive column after Applying Partition by

Question

I have data as follows:

+----+--------+------------------+
| Id | Weight | is_weight_faulty |
+----+--------+------------------+
| A  |    100 |                1 |
| A  |     50 |                0 |
| A  |     10 |                0 |
| B  |    500 |                0 |
| B  |    200 |                0 |
| B  |     40 |                0 |
| C  |    100 |                0 |
+----+--------+------------------+

I need to create a column correct weight which depends on two factors Id and value is_weight_faulty. First I will apply partition by Id and order by is_weight_faulty. If for any subset we find value 1 in column is_weight_faulty our correct weight would be Weight = Correct_Weight where is_weight_faulty = 1 and for remaining entries of that subset Correct_Weight would be 0.

If we look at first three rows for Id A Correct_Weight would equal 100 in 1st row while for next 2 rows it would be 0. For remaining cases Correct_Weight would equal Weight as there is no record of is_weight_faulty = 1 against each subset of data that we would get after applying partition by.

Desired Output

+----+--------+------------------+----------------+
| Id | Weight | is_weight_faulty | Correct_Weight |
+----+--------+------------------+----------------+
| A  |    100 |                1 |            100 |
| A  |     50 |                0 |              0 |
| A  |     10 |                0 |              0 |
| B  |    500 |                0 |            500 |
| B  |    200 |                0 |            200 |
| B  |     40 |                0 |             40 |
| C  |    100 |                0 |            100 |
+----+--------+------------------+----------------+

I am unable to create cases that would make Correct_Weight 0 in case if there is an entry of is_weight_faulty = 1

What have you tried? Where did you get stuck?

Dale K
– Dale K

2021-04-06 09:32:44 +00:00
Commented Apr 6, 2021 at 9:32 — Dale K
– Dale K, Commented Apr 6, 2021 at 9:32

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-04-06 09:35:47Z

1

You can use max()over() window function to find out the highest value of is_weight_correct column for each id. If it's 1 then multiply weight with is_weight_correct with the help of case when else statement.

Schema and insert statements:

 create table mytable ( Id varchar(10), Weight int, is_weight_faulty int);
 
 insert into mytable values( 'A'  ,    100 ,                1 );
 insert into mytable values( 'A'  ,     50 ,                0 );
 insert into mytable values( 'A'  ,     10 ,                0 );
 insert into mytable values( 'B'  ,    500 ,                0 );
 insert into mytable values( 'B'  ,    200 ,                0 );
 insert into mytable values( 'B'  ,     40 ,                0 );
 insert into mytable values( 'C'  ,    100 ,                0 );

Query:

 select id, weight, is_weight_faulty, 
 (case when max(is_weight_faulty)over(partition by id)=1 then weight*is_weight_faulty else weight end) correct_weight
 from mytable
 GO

Output:

id	weight	is_weight_faulty	correct_weight
A	100	1	100
A	50	0	0
A	10	0	0
B	500	0	500
B	200	0	200
B	40	0	40
C	100	0	100

db<fiddle here

answered Apr 6, 2021 at 9:35

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

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

2 Comments

Lopez Over a year ago

can you explain this part of your code (partition by id)=1

Kazi Mohammad Ali Nur Romel Over a year ago

(max(is_weight_faulty)over(partition by id)) is calculating max(is_weight_faulty) for each ID. If it's 1 that means for that particular id at least one row has 1 in is_weight_faulty column

Collectives™ on Stack Overflow

Derive column after Applying Partition by

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related