CASE statement if no rows found | Oracle

user_10071566 0
Viewed 76

I have a sample table with below data. i am using Oracle.

ID  Sales  STATUS
1    100   XX
1    50    XX
1    40    YY

I have to group the data on ID column having different status. I am using below CASE query

SELECT 
ID, 
sum(sales) SALES,
CASE 
WHEN STATUS='XX' THEN 'POSITIVE'
WHEN STATUS='YY' THEN 'NEGATIVE'
END INDICATOR
from 
sample_table
group by 
ID,
CASE 
WHEN STATUS='XX' THEN 'POSITIVE'
WHEN STATUS='YY' THEN 'NEGATIVE'
END;

The above statement is giving me correct output which is

ID    SALES    INDICATOR
1     150      POSITIVE
1     40       NEGATIVE

Now if i change the data to below

ID  Sales  STATUS
1    100   XX
1    50    XX
1    40    XX

The query used above is giving only POSITIVE indicator row as there is no YY STATUS in the table. I want to display 0 SALES even if there are no status YY for the same ID.

OUTPUT
ID   SALES   STATUS
1    190     POSITIVE

Desired output
ID   SALES   STATUS
1     190     POSITIVE
1     0       NEGATIVE

is it possible to have the desired output? It's a rare scenario for my data but i still want to handle it. Thank you for the help in advance.

case oracle
3 Answers

You could do a PIVOT, then UNPIVOT combined with NVL so that you always get a positive and negative indicator for each ID.

Case 1

WITH
    sample_table (ID, Sales, STATUS)
    AS
        (SELECT 1, 100, 'XX' FROM DUAL
         UNION ALL
         SELECT 1, 50, 'XX' FROM DUAL
         UNION ALL
         SELECT 1, 40, 'YY' FROM DUAL)
SELECT *
  FROM (SELECT id, NVL (indicator_pos, 0) AS indicator_pos, NVL (indicator_neg, 0) AS indicator_neg
          FROM (SELECT ID,
                       SALES,
                       CASE
                           WHEN STATUS = 'XX' THEN 'POSITIVE'
                           WHEN STATUS = 'YY' THEN 'NEGATIVE'
                       END    INDICATOR
                  FROM sample_table)
               PIVOT (SUM (sales)
                     FOR INDICATOR
                     IN ('POSITIVE' AS indicator_pos, 'NEGATIVE' AS indicator_neg)))
       UNPIVOT (sales FOR INDICATOR IN (indicator_pos AS 'POSITIVE', indicator_neg AS 'NEGATIVE'));

   ID    INDICATOR    SALES
_____ ____________ ________
    1 POSITIVE          150
    1 NEGATIVE           40

Case 2

WITH
    sample_table (ID, Sales, STATUS)
    AS
        (SELECT 1, 100, 'XX' FROM DUAL
         UNION ALL
         SELECT 1, 50, 'XX' FROM DUAL
         UNION ALL
         SELECT 1, 40, 'XX' FROM DUAL)
SELECT *
  FROM (SELECT id, NVL (indicator_pos, 0) AS indicator_pos, NVL (indicator_neg, 0) AS indicator_neg
          FROM (SELECT ID,
                       SALES,
                       CASE
                           WHEN STATUS = 'XX' THEN 'POSITIVE'
                           WHEN STATUS = 'YY' THEN 'NEGATIVE'
                       END    INDICATOR
                  FROM sample_table)
               PIVOT (SUM (sales)
                     FOR INDICATOR
                     IN ('POSITIVE' AS indicator_pos, 'NEGATIVE' AS indicator_neg)))
       UNPIVOT (sales FOR INDICATOR IN (indicator_pos AS 'POSITIVE', indicator_neg AS 'NEGATIVE'));

   ID    INDICATOR    SALES
_____ ____________ ________
    1 POSITIVE          190
    1 NEGATIVE            0
user_71100990

You can add the dummy data to handle this scenario -

SELECT ID, 
       SUM(sales) SALES,
       CASE WHEN STATUS='XX' THEN 'POSITIVE'
            WHEN STATUS='YY' THEN 'NEGATIVE'
       END INDICATOR
  FROM (SELECT ID,
               sales,
               STATUS
          FROM sample_table
        -- ADDING A DUMMY ROW
         UNION ALL
        SELECT DISTINCT ID, 0, 'YY'
          FROM sample_table)
 GROUP BY ID,
          CASE WHEN STATUS='XX' THEN 'POSITIVE'
               WHEN STATUS='YY' THEN 'NEGATIVE'
          END;
user_36277560

There's a lot of ways to solve this. I like starting with defining the columns I want to always appear, and then outer joining the table to use for aggregate queries.

WITH
    sample_table (ID, Sales, STATUS)
    AS
        (SELECT 1, 100, 'XX' FROM DUAL
         UNION ALL
         SELECT 1, 50, 'XX' FROM DUAL
         UNION ALL
         SELECT 1, 40, 'XX' FROM DUAL
         UNION ALL
         SELECT 2, 50, 'YY' FROM DUAL)
SELECT 
ID2, 
sum(nvl(sales,0)) SALES,
CASE 
WHEN STATUS2='XX' THEN 'POSITIVE'
WHEN STATUS2='YY' THEN 'NEGATIVE'
END INDICATOR
from 
(select distinct t1.id id2, t2.status status2 
    from sample_table t1 cross join sample_table t2) base_table
left outer join sample_table on id=id2 and status=status2  
group by 
ID2,
CASE 
WHEN STATUS2='XX' THEN 'POSITIVE'
WHEN STATUS2='YY' THEN 'NEGATIVE'
END
order by 1;

Output:

       ID2      SALES INDICATOR
---------- ---------- ---------
         1          0 NEGATIVE 
         1        190 POSITIVE 
         2         50 NEGATIVE 
         2          0 POSITIVE
user_30618520
Related