Oracle SQL Calculate Difference to one distinct row from sum of rows-CodePudding

I have the following table:

CREATE TABLE tablename ("ID" varchar2(1), "Type" varchar2(3), "Value" int);

INSERT ALL 
    INTO tablename ("ID", "Type", "Value")
         VALUES ('A', 'MS',2)
    INTO tablename ("ID","Type", "Value")
         VALUES ('A', 'MS', 5)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('A', 'MSH', 6)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('A', 'MSH', 10)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('A', 'MSO', -5)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('A', 'MSO', 12)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('B', 'MS',5)
    INTO tablename ("ID","Type", "Value")
         VALUES ('B', 'MS', -4)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('B', 'MSH', 2)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('B', 'MSH', 11)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('B', 'MSO', -5)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('B', 'MSO', 13)
SELECT * FROM dual
;

The table will be grouped by ID and Type and use the sum of the Values. Now I want to get the difference of MS-MSH and MS-MSO for each ID.

So the result should be something like

ID | Type | sum(value) | Dif
A  | MS   |  7         | 0
A  | MSH  |  16        | -9
A  | MSO  |  7         | 0
B  | MS   |  1         | 0
B  | MH   |  13        | -12
B  | MSO  |  9         | -8

Here is the table to work with

CodePudding user response：

(More of a comment) I've not tested, i'd try something along these lines. Hope this is of some help. Aologies, i haven't tested.

with cte as 
(
    select t.id,t.type,sum(t.value) as  sumval
    from 
    tablename as t
    group by t.id,t.type
)
select c.*,
case c.type
    when 'ms' then 0
    else (c.sumval-(select c2.sumval from cte  c2 where c2.id=c.id and c2.type='MS'))
end dif
from cte c
order by c.id,c.type

CodePudding user response：

For 'MSH' or 'MSO' "Type" the query is searching in an inline view for Sum_value of that "Type" and "ID" then calculating 'MS'- 'MSH' or 'MSO'

THE QUERY:

select X."ID",X."Type", X.Sum_value,
case X."Type"
    when 'MS' then 0
    ELSE
        (select Sum_value from (select "ID","Type",sum("Value") Sum_value from tablename
        group by ("ID","Type") order by "ID","Type") Y
        where Y."Type"='MS' and Y."ID"=X."ID")- X.Sum_value
end diff
from (
        select "ID","Type",sum("Value") Sum_value
        from tablename
        group by ("ID","Type")
        order by "ID","Type"
        ) X

SAMPLE DATA:

CREATE TABLE tablename ("ID" varchar2(1), "Type" varchar2(3), "Value" int);

INSERT ALL 
    INTO tablename ("ID", "Type", "Value")
         VALUES ('A', 'MS',2)
    INTO tablename ("ID","Type", "Value")
         VALUES ('A', 'MS', 5)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('A', 'MSH', 6)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('A', 'MSH', 10)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('A', 'MSO', -5)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('A', 'MSO', 12)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('B', 'MS',5)
    INTO tablename ("ID","Type", "Value")
         VALUES ('B', 'MS', -4)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('B', 'MSH', 2)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('B', 'MSH', 11)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('B', 'MSO', -5)
    INTO tablename ("ID", "Type", "Value")
         VALUES ('B', 'MSO', 13)
SELECT * FROM dual
;

RESULT:

ID Type SUM_VALUE DIFF
----------------------
A   MS   7        0
A   MSH  16       -9
A   MSO  7        0
B   MS   1        0
B   MSH  13       -12
B   MSO  8        -7

CodePudding user response：

With the LAG analytic function:

SQL> with temp as
  2    (select id, type, sum(value) sumval
  3     from tablename
  4     group by id, type
  5    )
  6  select id, type, sumval,
  7    --
  8    -case when type = 'MS'  then 0
  9         when type = 'MSH' then sumval - lag(sumval, 1) over (partition by id order by type)
 10         when type = 'MSO' then sumval - lag(sumval, 2) over (partition by id order by type)
 11    end diff
 12  from temp
 13  order by id, type;

ID TYPE     SUMVAL       DIFF
-- ---- ---------- ----------
A  MS            7          0
A  MSH          16         -9
A  MSO           7          0
B  MS            1          0
B  MSH          13        -12
B  MSO           8         -7

6 rows selected.

SQL>

Not related to your problem, but - this is Oracle. Avoid double quotes and mixed case identifiers, you'll only have problems with these.