someone new someone new - 5 months ago 11
SQL Question

SQL: Return '0' for a row if it doesn't exist

I have a SQL query which displays

count
,
date
, and
time
.

This is what the output looks like:

enter image description here

And this is my SQL query:

select
count(*),
to_char(timestamp, 'MM/DD/YYYY'),
to_char(timestamp, 'HH24')
from
MY_TABLE
where
timestamp >= to_timestamp('03/01/2016','MM/DD/YYYY')
group by
to_char(timestamp, 'MM/DD/YYYY'), to_char(timestamp, 'HH24')


Now, in
COUNT
column, I want to display
0
if the count doesn't exist for that hour. So on
3/2/2016
at
8am
, the
count
was
6
. Then at
9am
the count was
0
so that row didn't get displayed. I want to display that row. And at
10am
&
11am
, the counts are displayed then it just goes to next day.

So how do I display
count
of
0
? I want to display
0
count for each day every hour doesn't matter if it's 0 or 6 or whatever. Thanks :)

vkp vkp
Answer

Use a cte to generate numbers for all the hours in a day. Then cross join the result with all the possible dates from the table. Then left join on the cte which has all date and hour combinations, to get a 0 count when a row is absent for a particular hour.

with nums(n) as (select 1 from dual
                 union all 
                 select n+1 from nums where n < 24)
,dateshrscomb as (select n,dt 
                   from nums 
                   cross join (select distinct trunc(timestamp) dt from my_table 
                               where timestamp >= to_timestamp('03/01/2016','MM/DD/YYYY')
                               ) alldates
                   )
select count(trunc(m.timestamp)), d.dt, d.n
from dateshrscomb d
left join MY_TABLE m on to_char(m.timestamp, 'HH24') = d.n  
and trunc(m.timestamp) = d.dt
and m.timestamp >= to_timestamp('03/01/2016','MM/DD/YYYY')
group by d.dt, d.n