I need to get 3 data

1. The sum of data between n dates / days like, I need to get the sum of 01-12 to 05-12 and this / days = 5

2. The result of 1 but this is for validation

3. The difference between #1 and #2

How to get the sum of data from different dates also I need this 3 points for every segment I have like

select segment,
       (SELECT var1 AS 1.-
                 ( SELECT var1 AS 1.-
                   from table 
                   where data = 20221207
                   group by segement
                 )
        from   table
        where  data = 20221206
        group by segement) AS 1.-,
        sum(IMP_SDO_MED_CONT_ML) AS 2.-,
        (1.- - 2.-) AS difference,
from    table
WHERE   DATA = 20221207
group by segment;

CodePudding user response：

You appear to want to sum the data using conditional aggregation and then can find #3 using subtraction:

SELECT segment,
       SUM(CASE WHEN data BETWEEN 20221201 AND 20221212 THEN var1 END) AS "1",
       SUM(IMP_SDO_MED_CONT_ML) AS "2",
       SUM(CASE WHEN data BETWEEN 20221201 AND 20221212 THEN var1 END)
       - SUM(IMP_SDO_MED_CONT_ML) AS "3"
FROM   table_name
GROUP BY segment

However, without sample input data it is difficult to check what you are expecting.

CodePudding user response：

It's realy hard to tell what the question is without the data. Maybe it is about calculating the difference of sums of two columns in some period per segments. If that is the case then the query would be:

Select 
    SEGMENT,
    Sum(var_1) "FLD_1",
    Sum(IMP_SDO_MED_CONT_ML) "FLD_2",
    Sum(var_1) - Sum(IMP_SDO_MED_CONT_ML) "DIFF"
From    a_tbl
Where   DATE_NUMBER Between 20221201 And 20221203
Group By SEGMENT
Order By SEGMENT

... and if we invent some sample data like below

WITH
    a_tbl AS
        (
            Select 'AAA' "SEGMENT", 1234 "VAR_1", 1233 "IMP_SDO_MED_CONT_ML", 20221201 "DATE_NUMBER" From Dual Union All
            Select 'AAA' "SEGMENT", 5678 "VAR_1", 5677 "IMP_SDO_MED_CONT_ML", 20221202 "DATE_NUMBER" From Dual Union All
            Select 'AAA' "SEGMENT", 9101 "VAR_1", 9103 "IMP_SDO_MED_CONT_ML", 20221203 "DATE_NUMBER" From Dual Union All
            Select 'BBB' "SEGMENT", 8765 "VAR_1", 8766 "IMP_SDO_MED_CONT_ML", 20221201 "DATE_NUMBER" From Dual Union All
            Select 'BBB' "SEGMENT", 6666 "VAR_1", 6665 "IMP_SDO_MED_CONT_ML", 20221202 "DATE_NUMBER" From Dual Union All
            Select 'BBB' "SEGMENT", 4423 "VAR_1", 4420 "IMP_SDO_MED_CONT_ML", 20221203 "DATE_NUMBER" From Dual Union All
            Select 'CCC' "SEGMENT", 1234 "VAR_1", 1233 "IMP_SDO_MED_CONT_ML", 20221201 "DATE_NUMBER" From Dual Union All
            Select 'CCC' "SEGMENT", 5678 "VAR_1", 5677 "IMP_SDO_MED_CONT_ML", 20221203 "DATE_NUMBER" From Dual Union All
            Select 'DDD' "SEGMENT", 1234 "VAR_1", 1233 "IMP_SDO_MED_CONT_ML", 20221201 "DATE_NUMBER" From Dual Union All
            Select 'EEE' "SEGMENT", 5678 "VAR_1", 5678 "IMP_SDO_MED_CONT_ML", 20221203 "DATE_NUMBER" From Dual 
        )

... then the result would be

OR maybe it is the difference between sum of the period for one column and sum of the last day of period for the other column. In that case query could be:

Select 
    SEGMENT,
  Sum(var_1) "FLD_1",
  Sum(CASE WHEN DATE_NUMBER = 20221203 THEN IMP_SDO_MED_CONT_ML ELSE 0 END) "FLD_2",
  Sum(var_1) - Sum(CASE WHEN DATE_NUMBER = 20221203 THEN IMP_SDO_MED_CONT_ML ELSE 0 END) "DIFF"
From    a_tbl
Where   DATE_NUMBER Between 20221201 And 20221203
Group By SEGMENT
Order By SEGMENT

... resulting (with same invented data) as