2

I have one table as below

    A   B   C   D   E
2471    D471  0   24.00   1
2471    D471  0   353.50  1
2471    D471  1   211.00  1

2471    E471  1   343.00  1
2471    E471  0   56.00   1
2471    E471  0   177.06  1
2471    E471  0   9.31    1

2471    F471  0   10.31   1
2471    F471  1   10.31   1

I need to group on A,B and C and need to sum the value of E, example table below how it looks after summing up

    A   B   C   E   SumValue
2471    D471  0   1 377.500000
2471    D471  1   1 211.000000

2471    E471  1   1 343.000000
2471    E471  0   1 242.370000

2471    F471  0   1 10.31
2471    F471  1   1 10.31

I need to compare the SumValue by grouping A,B,C of first row which C has 0 with the second row which C has 1 and which ever has the lowest value those records should come as 1 in E column rest of the records should come as 0 in E column. If the SumValue is same whereas in the last two rows then E column should come as 1 where C is 1 and E column should come as 0 where C is 0.

The output I need is like this...All I need to do in this scenario is updating E column..

    A   B   C   D   E
2471    D471  0   24.00   0
2471    D471  0   353.50  0
2471    D471  1   211.00  1

2471    E471  1   343.00  0
2471    E471  0   56.00   1
2471    E471  0   177.06  1
2471    E471  0   9.31    1

2471    F471  0   10.31   0
2471    F471  1   10.31   1

Scripts to create table and insert data

CREATE TABLE tablename
    ([A] int, [B] varchar(10), [C] int, [D] decimal(10, 2), [E] int)
;

INSERT INTO tablename
    ([A], [B], [C], [D], [E])
VALUES
    (2471, 'D471', 0, 24.00, 1),
    (2471, 'D471', 0, 353.50, 1),
    (2471, 'D471', 1, 211.00, 1),
    (2471, 'E471', 1, 343.00, 1),
    (2471, 'E471', 0, 56.00, 1),
    (2471, 'E471', 0, 177.06, 1),
    (2471, 'E471', 0, 9.31, 1),
    (2471, 'F471', 0, 10.31, 1),
    (2471, 'F471', 1, 10.31, 1)
;

If you have any questions on this query, please let me know

Inc
  • 55
  • 4

1 Answers1

2

If I understand correctly:

with toupdate as (
      select t.*,
             dense_rank() over (partition by a, b order by sum_d, c desc) as seqnum
      from (select t.*, sum(d) over (partition by a, b, c) as sum_d
            from tablename t
           ) t
     )
update toupdate
    set e = (case when seqnum = 1 then 1 else 0 end);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • It is giving the expected output in case of sum_d is higher or lesser when partitioned on a,b,c columns, but when the sum_d is equal when partitioned by a,b,c columns then I need the output to be 1 in E column where C column has 1 other row should come as 0 (the row where C column has 0) – Inc Mar 26 '20 at 14:23
  • 1
    @James . . . I follow that logic better in your comment. That just requires a second key on the `order by` in the `dense_rank()`. – Gordon Linoff Mar 26 '20 at 14:28