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 Apr 06 '21 at 09:32 — Dale K, Apr 06 '21 at 09:32

score 1 · Accepted Answer · answered Apr 06 '21 at 09:35

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

can you explain this part of your code `(partition by id)=1` — Lopez, Apr 06 '21 at 10:01
(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 — Kazi Mohammad Ali Nur Romel, Apr 06 '21 at 10:03

Derive column after Applying Partition by

1 Answers1