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

user_9692406 0
Viewed 60

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

oracle sql
4 Answers

(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
user_54147980

Query works if u have "Types" like 'MS1', 'MSH1', 'MSO1', 'MS2', 'MSO2'... , diff is partitioned by "ID" and the number on end of the "Type" string, query does only 1 look at the table no sub-querys and cte, no case statement.

QUERY:

 select "ID","Type",sum("Value") Sum_value, 
        FIRST_VALUE (sum("Value")) over (partition by "ID",
        nvl(regexp_substr("Type",'[0-9]{1,}$'),'0')
        ORDER BY "ID")-sum("Value")
        diff
 from tablename
 group by ("ID","Type")

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
user_183307870

You can wrap the aggregation functions in an analytic function and use conditional aggregation to find the MS value for type with the same id (then you only read the table once and do not need any correlated sub-queries or CTEs):

SELECT id,
       type,
       sum(value) as sumval,
       MAX(CASE type WHEN 'MS' THEN SUM(value) END) OVER (PARTITION BY id)
         - SUM(value) AS diff
FROM   tablename
GROUP BY
       id,
       type;

Which, for the sample data:

CREATE TABLE tablename (ID, Type, Value) AS
SELECT 'A', 'MS',   2 FROM DUAL UNION ALL
SELECT 'A', 'MS',   5 FROM DUAL UNION ALL
SELECT 'A', 'MSH',  6 FROM DUAL UNION ALL
SELECT 'A', 'MSH', 10 FROM DUAL UNION ALL
SELECT 'A', 'MSO', -5 FROM DUAL UNION ALL
SELECT 'A', 'MSO', 12 FROM DUAL UNION ALL
SELECT 'B', 'MS',   5 FROM DUAL UNION ALL
SELECT 'B', 'MS',  -4 FROM DUAL UNION ALL
SELECT 'B', 'MSH',  2 FROM DUAL UNION ALL
SELECT 'B', 'MSH', 11 FROM DUAL UNION ALL
SELECT 'B', 'MSO', -5 FROM DUAL UNION ALL
SELECT 'B', 'MSO', 13 FROM DUAL;

Outputs:

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

db<>fiddle here

user_15092640

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.

user_90979060
Related