CODEWITHSUNDEEP
sqloracle-databasecase
George M
George M

Reputation: 13

Display blank rows from a case expression

I am writing a sql report, and one of the requirements asked of me is the inclusion of rows for both options of a CASE expression.

The report is to show the count of "Costing" and "Non-costing" transactions between all customers in the database and each of the 3 chosen customers.

Please see the example layout required.

CUSTOMER_ID | CUSTOMER_NAME | COSTING     | CUST_1 | CUST_2 | CUST_3
12          | Red Corp      | Non-costing | 1245   | 734    | 84
12          | Red Corp      | Costing     | 376    | 23     | 2
17          | Blue Corp     | Non-costing | 4538   | 36     | 3256
17          | Blue Corp     | Costing     | 0      | 0      | 0
...

I have tried putting the COSTING column within a case statement. However I cannot get the example "Blue Corp" "Costing" row to display in the output due to it being blank. 

select  CUSTOMER_ID
        ,CUSTOMER_NAME
        ,case 
            when PURCHASE_VALUE = 0 or PURCHASE_VALUE is null then 'Non-costing'
            when PURCHASE_VALUE != 0 or PURCHASE_VALUE is not null then 'Costing'
        end AS COSTING
,count(CASE WHEN CUSTOMER_ID = 11 then 1 ELSE NULL END) as "CUST_1"
,count(CASE WHEN CUSTOMER_ID = 22 then 1 ELSE NULL END) as "CUST_2"
,count(CASE WHEN CUSTOMER_ID = 33 then 1 ELSE NULL END) as "CUST_3"
from schema.CUSTOMER_TRANSACTIONS
group by case 
            when PURCHASE_VALUE = 0 or PURCHASE_VALUE is null then 'Non-costing'
            when PURCHASE_VALUE != 0 or PURCHASE_VALUE is not null then 'Costing'
        end 
        ,CUSTOMER_ID
        ,CUSTOMER_NAME

I am currently getting the same as the example, but missing the 0 count row. Please see the output I am currently getting below.

CUSTOMER_ID | CUSTOMER_NAME | COSTING     | CUST_1 | CUST_2 | CUST_3
12          | Red Corp      | Non-costing | 1245   | 734    | 84
12          | Red Corp      | Costing     | 376    | 23     | 2
17          | Blue Corp     | Non-costing | 4538   | 36     | 3256
...

Upvotes: 1

Views: 1734

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Use a cross join to generate the rows and a left join to add the columns:

select c.CUSTOMER_ID, c.CUSTOMER_NAME, t.costing,
       sum(CASE WHEN CUSTOMER_ID = 11 then 1 ELSE 0 END) as CUST_1,
       sum(CASE WHEN CUSTOMER_ID = 22 then 1 ELSE 0 END) as CUST_2,
       sum(CASE WHEN CUSTOMER_ID = 33 then 1 ELSE 0 END) as CUST_3
from (select distinct ct.customer_id, ct.customer_name
      from schema.CUSTOMER_TRANSACTIONS ct
     ) c cross join
     (select 'Non-costing' as costing from dual union all
      select 'Costing' from dual
     ) t left join
     schema.customer_transactions ct
     on c.customer_id = ct.customer_id,
        t.costing = (case when PURCHASE_VALUE = 0 or PURCHASE_VALUE is null
                         then 'Non-costing'
                         else 'Costing'
                     end)
group by c.CUSTOMER_ID, c.CUSTOMER_NAME, t.type;

Upvotes: 2

B. Seberle
B. Seberle

Reputation: 395

You might try this. It will give you 0 when there is none.

select  CUSTOMER_ID
        ,CUSTOMER_NAME
, CASE WHEN ISNULL(PURCHASE_Value,0) = 0 THEN 'Non-costing' ELSE 'Costing' END        
,SUM(CASE WHEN CUSTOMER_ID = 11 then 1 ELSE 0 END) as "CUST_1"
,SUM(CASE WHEN CUSTOMER_ID = 22 then 1 ELSE 0 END) as "CUST_2"
,SUM(CASE WHEN CUSTOMER_ID = 33 then 1 ELSE 0 END) as "CUST_3"
from schema.CUSTOMER_TRANSACTIONS
group by CUSTOMER_ID
        ,CUSTOMER_NAME
,PURCHASE_Value

Upvotes: 0

Popeye
Popeye

Reputation: 35900

From your comment,

Alex Poole, you are correct, there is no data for "Costing" for "Blue-corp". However I want the row to display with 0s rather than not appearing at all.

I think you need to generate the record for which one of the costings is not available.

I tried to create the query which will generate the record if "Costing" or "Non-costing" record is not available for any customer.

I was not able to test it so please share if you experience any issue.

-- Your original query starts from here
WITH DATAA AS (select  CUSTOMER_ID
        ,CUSTOMER_NAME
        ,case 
            when PURCHASE_VALUE = 0 or PURCHASE_VALUE is null then 'Non-costing'
            when PURCHASE_VALUE != 0 or PURCHASE_VALUE is not null then 'Costng'
        end AS COSTING
,count(CASE WHEN CUSTOMER_ID = 11 then 1 ELSE NULL END) as "CUST_1"
,count(CASE WHEN CUSTOMER_ID = 22 then 1 ELSE NULL END) as "CUST_2"
,count(CASE WHEN CUSTOMER_ID = 33 then 1 ELSE NULL END) as "CUST_3"
from schema.CUSTOMER_TRANSACTIONS
group by case 
            when PURCHASE_VALUE = 0 or PURCHASE_VALUE is null then 'Non-costing'
            when PURCHASE_VALUE != 0 or PURCHASE_VALUE is not null then 'Costing'
        end 
        ,CUSTOMER_ID
        ,CUSTOMER_NAME)
-- Your original query ends here


-- Solution
SELECT
    CUSTOMER_ID,
    CUSTOMER_NAME,
    COSTING,
    CUST_1,
    CUST_2,
    CUST_3
FROM
    DATAA
UNION ALL
( SELECT
    CUSTOMER_ID,
    CUSTOMER_NAME,
    CASE
        WHEN COUNT(CASE
            WHEN COSTING = 'Non-costing' THEN 1
        END) = 0 THEN 'Non-costing'
        WHEN COUNT(CASE
            WHEN COSTING = 'Costing' THEN 1
        END) = 0 THEN 'Costing'
    END AS COSTING,
    0,
    0,
    0
FROM
    DATAA D1
GROUP BY
    CUSTOMER_ID,
    CUSTOMER_NAME
HAVING ( COUNT(CASE
    WHEN COSTING = 'Non-costing' THEN 1
END) = 0
         OR COUNT(CASE
    WHEN COSTING = 'Costng' THEN 1
END) = 0 )
);

Cheers!!

Upvotes: 1

Related Questions