Show Zero If There Is No Record Count - Oracle SQL Query

Show Zero if there is no record count - ORACLE SQL query

Create a CTE for the subquery and use UNION ALL with NOT EXISTS to cover the case that the CTE does not return any rows:

WITH cte AS (
  SELECT error_message, serial_num, COUNT(*) total 
  FROM Table1 
  WHERE error_message NOT LIKE '%INVALID%' 
  GROUP BY error_message, serial_num
)
SELECT 
  1 as Index_Num, 
  CONCAT(
    'Different Errors:  ', 
    list_agg(error_message || '# ' || serial_num) within group (order by error_message)
  ), 
  SUM(total) 
FROM cte
UNION ALL
SELECT 1, 'Different Errors:  ', 0
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM cte)

return 0 in select count when no record found

Just use:

SELECT max(id) as id, count(*)
FROM table1
WHERE reference_id = 300000009798620

SQL Query Count results as zero event record is not exist

You want a left join and aggregation:

select r.reason, count(a.reason)
from reasons r left join
     accidents a
     on r.reason = a.reason and
        a.date_of_accident >= date '2017-01-01' and
        a.date_of_accident < date '2018-01-01'
group by r.reason;

How to write an SQL query that returns count = 0 when no records found in group

You could start of with a select on the genders (using an inline view), then do a left outer join on the results. This will guarantee that there is always a list of genders available to show in the first column:

SELECT g.gender
,      count(ID) "Count"
FROM   ( select 'Female' gender from dual
         union all
         select 'Male' gender from dual
         union all
         select 'Unknown' gender from dual
       ) g
left
outer
join   client c
on     decode(c.gender, 'F', 'Female', 'M', 'Male', 'Unknown') = g.gender
and    c.created <= '01-JAN-2000'
GROUP
BY     g.gender
ORDER
BY     g.gender
;

ORACLE display count to zero, when row is null in column

You could group your results by name and then left join the result to a table of your names to fill in the blanks:

SELECT    rn.name, NVL(cnt, 0)
FROM      (SELECT 'Jenny' AS name FROM dual
           UNION ALL
           SELECT 'Penny' FROM dual
           UNION ALL
           SELECT 'George' FROM dual
           UNION ALL
           SELECT 'James' FROM dual
           UNION ALL
           SELECT 'Jessica' FROM dual
           UNION ALL
           SELECT 'Monica' FROM dual
           UNION ALL
           SELECT 'Erica' FROM dual) rn
LEFT JOIN (SELECT   name, COUNT(*) AS cnt
           FROM     namestable
           WHERE    adeddate BETWEEN '2014/10/15' AND '2014/10/16' 
           GROUP BY name) n ON n.name = rn.name

Need query to return zero when count(*) doesn't match any records

You need to generate a list of the dates you're interested in, and then do a left outer join to your table to find records that match each date. Something like this:

with tmp_dates as (
    select trunc(sysdate) - level + 1 as tmp_date
    from dual
    connect by level <= 10
)
select count(tt.test_date_create), td.tmp_date
from tmp_dates td
left join test_table tt on trunc(tt.test_date_create) = td.tmp_date
group by td.tmp_date
order by tmp_date;

The common table expression on its own generates a list of dates:

select trunc(sysdate) - level + 1 as tmp_date
from dual
connect by level <= 10;

TMP_DATE
---------
10-JUN-13 
09-JUN-13 
08-JUN-13 
07-JUN-13 
06-JUN-13 
05-JUN-13 
04-JUN-13 
03-JUN-13 
02-JUN-13 
01-JUN-13 

You can adjust the level limit and how it's added to sysdate to modify the range, e.g. to look further back or to exclude today.

The main select then uses that to look for matching records. Because it is an outer join it lists all the generated dates, and a count of zero where there are no matches.

SQL Fiddle.

select statement should return count as zero if no row return using group by clause

You should store somewhere your statuses (pherhaps in another table). Otherwise, you list them using subquery:

with stored_statuses as (
  select 'P' code, 'present' description from dual
  union all 
  select 'A' code, 'absent' description from dual
  union all 
  select 'S' code, 'ill' description from dual
  union all 
  select 'T' code, 'transfer' description from dual
  union all 
  select 'L' code, 'left' description from dual
)
select ss.code, count(*) from student_info si
left join stored_statuses ss on ss.code = si.status
group by ss.code

---

Related Topics