CODEWITHSUNDEEP
sqloracle-databasewindow-functions
scholarwithfire
scholarwithfire

Reputation: 365

Oracle SQL - Sum Partition over ID but exclude repeated

My Table :

HEAD_ID  LINE ID  ADJUST_ID  LIST_PRICE  DISCOUNT
01       01       01         200         15
01       01       02         200         0
01       01       03         200         10
01       02       01         300         16
01       02       02         300         0
02       01       01         300         15
02       01       02         300         0
02       01       03         300         10
02       02       01         100         16
02       02       02         100         0

I need a sum over LIST_PRICE group by HEAD_ID and LINE_ID but exclude the ADJUST_ID. The TOTAL_LIST_PRICE should be the sum of all line ID from a head id but not repeated. For example : 

For the TOTAL_LIST_PRICE for HEAD_ID 01 is (HEAD_ID 01 LINE_ID 01) + (HEAD_ID 01 LINE_ID 02) = 200 + 300 = 500
For the TOTAL_LIST_PRICE for HEAD_ID 02 is (HEAD_ID 02 LINE_ID 01) + (HEAD_ID 02 LINE_ID 02) = 300 + 105 = 405

Result expected is like this :

HEAD_ID  LINE ID  ADJUST_ID  LIST_PRICE  DISCOUNT  TOTAL_LIST_PRICE
01       01       01         200         15        500
01       01       02         200         0         500
01       01       03         200         10        500
01       02       01         300         16        500
01       02       02         300         0         500
02       01       01         300         15        405 
02       01       02         300         0         405
02       01       03         300         10        405
02       02       01         105         16        405
02       02       02         105         0         405

MY query is like this -

SELECT head_id, line_id, adjust_id, list_price, discount,
   SUM(LIST_PRICE) OVER (PARTITION BY head_id, line_id) TOTAL_LIST_PRICE 
FROM TABLE;

The output - 

HEAD_ID  LINE ID  ADJUST_ID  LIST_PRICE  DISCOUNT  TOTAL_LIST_PRICE
01       01       01         200         15        600
01       01       02         200         0         600
01       01       03         200         10        600
01       02       01         300         16        600
01       02       02         300         0         600
02       01       01         300         15        900 
02       01       02         300         0         900
02       01       03         300         10        900
02       02       01         105         16        210
02       02       02         105         0         210

Is there anything I missing in the SUM OVER PARTITION? Or I'm using a wrong approach for this?

Thanks

Upvotes: 1

Views: 2410

Answers (2)

MT0
MT0

Reputation: 168470

You can use the ROW_NUMBER analytic function and PARTITION BY Head_id, Line_id, List_Price which will number each row for that partition and then filtering when that ROW_NUMBER is 1 will give the unique List_Price for each Head_id, Line_id which just needs to be SUMmed for each Head_id to give your desired output:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( HEAD_ID, LINE_ID, ADJUST_ID, LIST_PRICE, DISCOUNT ) AS
SELECT '01', '01', '01', 200, 15 FROM DUAL UNION ALL
SELECT '01', '01', '02', 200,  0 FROM DUAL UNION ALL
SELECT '01', '01', '03', 200, 10 FROM DUAL UNION ALL
SELECT '01', '02', '01', 300, 16 FROM DUAL UNION ALL
SELECT '01', '02', '02', 300,  0 FROM DUAL UNION ALL
SELECT '02', '01', '01', 300, 15 FROM DUAL UNION ALL
SELECT '02', '01', '02', 300,  0 FROM DUAL UNION ALL
SELECT '02', '01', '03', 300, 10 FROM DUAL UNION ALL
SELECT '02', '02', '01', 100, 16 FROM DUAL UNION ALL
SELECT '02', '02', '02', 100,  0 FROM DUAL;

Query 1:

SELECT HEAD_ID,
       LINE_ID,
       ADJUST_ID,
       LIST_PRICE,
       DISCOUNT,
       SUM( CASE rn WHEN 1 THEN LIST_PRICE END ) OVER ( PARTITION BY Head_id )
         AS total_list_price
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY Head_id, Line_id, List_Price
                             ORDER BY ROWNUM ) AS rn
  FROM   table_name t
)

Results:

| HEAD_ID | LINE_ID | ADJUST_ID | LIST_PRICE | DISCOUNT | TOTAL_LIST_PRICE |
|---------|---------|-----------|------------|----------|------------------|
|      01 |      01 |        01 |        200 |       15 |              500 |
|      01 |      01 |        02 |        200 |        0 |              500 |
|      01 |      01 |        03 |        200 |       10 |              500 |
|      01 |      02 |        01 |        300 |       16 |              500 |
|      01 |      02 |        02 |        300 |        0 |              500 |
|      02 |      01 |        01 |        300 |       15 |              400 |
|      02 |      01 |        02 |        300 |        0 |              400 |
|      02 |      01 |        03 |        300 |       10 |              400 |
|      02 |      02 |        01 |        100 |       16 |              400 |
|      02 |      02 |        02 |        100 |        0 |              400 |

Upvotes: 2

Edgars T.
Edgars T.

Reputation: 1139

select head_id, 
       line_id, 
       adjust_id, 
       list_price, 
       discount,
       sum(case when fl = 1 then list_price end) over(partition by head_id) total_list_price
from (select head_id, line_id, adjust_id, list_price, discount,
             row_number()over(partition by head_id, line_id order by head_id) fl
        from tab) q;

Upvotes: 2

Related Questions