CODEWITHSUNDEEP
sqloracle-databaseoracle11g
senthil
senthil

Reputation: 67

how to return a zero in sql instead of no row selected using case when

If I query a output that doesn't exist then I will get nothing returned. i'm looking for default (0) is returned in that scenario

select sum(case when a2.status='SUCCESS' THEN A2.a else 0 end) as success,
sum(case when a2.status='FAILED' THEN A2.a else 0 end) as failed,
sum(case when a2.status='ERROR' THEN A2.a else 0 end) as error
from
(select a.stauts,count(1) a 
from table1 a,table2 b
where a.id=b.id
a.date=sysdate
group by a.status)a2;

Note: There is no records for sysdate. I required default value "0" should be return for status.

Upvotes: 1

Views: 1687

Answers (6)

Kinnison84
Kinnison84

Reputation: 176

If I wanted to ensure there is always a result even for a query that wouldn't find any row to return, I would do a left join on dual table (for oracle):

select q.*  FROM DUAL d LEFT JOIN ( your_query )q on 1=1

This way you will always get back a row, no matter what!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This query should always return one row, even if nothing matches:

select sum(case when a.status = 'SUCCESS' then 1 else 0 end) as success,
       sum(case when a.status = 'FAILED' then 1 else 0 end) as failed,
       sum(case when a.status = 'ERROR' then 1 else 0 end) as error
from table1 a join
        table2 b
        on a.id = b.id
where a.date = trunc(sysdate);

Note that I changed the where logic. sysdate (despite its name) has a time component. If date has a time component, you may want:

where a.date >= trunc(sysdate) and a.date < trunc(sysdate + 1)

EDIT:

If the filter condition matches no rows, then you will get 0 using:

select count(case when a.status = 'SUCCESS' then 1 end) as success,
       count(case when a.status = 'FAILED' then 1 end) as failed,
       count(case when a.status = 'ERROR' then 1 end) as error
from table1 a join
        table2 b
        on a.id = b.id
where a.date = trunc(sysdate);

Upvotes: 2

dnoeth
dnoeth

Reputation: 60502

Aggregation without GROUP BY always returns a row, so your existing query will return NULLs.

To change a NULL to zero simply apply COALESCE:

select
   coalesce(sum(case when a2.status='SUCCESS' THEN A2.a end), 0) as success,
   coalesce(sum(case when a2.status='FAILED'  THEN A2.a end), 0) as failed,
   coalesce(sum(case when a2.status='ERROR'   THEN A2.a end), 0) as error
from
 (
   select a.status,count(1) a 
   from table1 a join table2 b
     on a.id=b.id
   where a.date=sysdate
   group by a.status
 ) a2;

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

If you use COUNT(), you don't need NVL() or COALESCE() to handle NULLs ,unlike the case for SUM(). COUNT() will always return a row with value=0 when the argument is NULL or when no rows are matched.GROUP BY too wouldn't be required.

SELECT COUNT(CASE WHEN a.status = 'SUCCESS' THEN 1 END) AS success,
       COUNT(CASE WHEN a.status = 'FAILED'  THEN 1 END) AS failed,
       COUNT(CASE WHEN a.status = 'ERROR'   THEN 1 END) AS error
FROM table1 a
JOIN table2 b ON a.id = b.id
WHERE a.date = TRUNC(SYSDATE);

If you just want to be clear, test these queries and pay attention to the result.

select SUM(1)    FROM DUAL WHERE 1=0; --NULL
select SUM(NULL) FROM DUAL WHERE 1=0; --NULL
select SUM(NULL) FROM DUAL WHERE 1=1; --NULL
select COUNT(1)  FROM DUAL WHERE 1=0;  -- 0
select COUNT(NULL) FROM DUAL WHERE 1=0; -- 0 
select COUNT(NULL) FROM DUAL WHERE 1=1; -- 0

Demo

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522722

The only suggestion which comes to mind would be to use a left join in your subquery and move the entire WHERE logic to the ON clause:

SELECT
    SUM(CASE WHEN a2.status = 'SUCCESS' THEN A2.a ELSE 0 END) AS success,
    SUM(CASE WHEN a2.status = 'FAILED'  THEN A2.a ELSE 0 END) AS failed,
    SUM(CASE WHEN a2.status = 'ERROR'   THEN A2.a ELSE 0 END) AS error
FROM
(
    SELECT a.status, COUNT(1) a 
    FROM table1 a
    LEFT JOIN table2 b
        ON a.id = b.id AND
           a.date = SYSDATE
    GROUP BY a.status
) a2;

Your current query is using archaic join syntax which makes it hard to see what is actually happening. In particular, it makes it hard to see whether or not you might be discarding information during the join which you wish to retain.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176174

You could generate missing values:

WITH cte AS (
     select a.status,count(1) a 
     from table1 a                  --JOIN syntax
     join table2 b
       on a.id=b.id
     WHERE a.date=sysdate           -- are you sure you want precision with time?
     group by a.status
), placeholder AS (
     SELECT *
     FROM cte
     UNION ALL
     SELECT *
     FROM (SELECT 'SUCCESS' AS status, 0 AS a FROM dual UNION ALL
           SELECT 'ERROR',   0 FROM dual UNION ALL
           SELECT 'FAILED',  0 FROM dual)  p
     WHERE NOT EXISTS (SELECT * FROM cte WHERE cte.status = p.status)
)
SELECT 
  sum(case when status='SUCCESS' THEN a else 0 end) as success,
  sum(case when status='FAILED' THEN a else 0 end) as failed,
  sum(case when status='ERROR' THEN a else 0 end) as error
FROM placeholder;

Upvotes: 0

Related Questions