2

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 :)

Improve this question
1
  • It's Oracle, not MySQL. Sorry Commented Jun 29, 2016 at 20:37

4 Answers 4

Reset to default
2

Use a partition outer join:

SELECT m.day,
       h.hr,
       COALESCE( freq, 0 ) AS freq
FROM   ( SELECT LEVEL - 1 AS hr
         FROM   DUAL
         CONNECT BY LEVEL <= 24
       ) h
       LEFT OUTER JOIN
       ( SELECT COUNT(*) AS freq,
                TO_CHAR( "timestamp", 'mm/dd/yyyy' ) AS day,
                EXTRACT( HOUR FROM "timestamp" ) AS hr
         FROM   MY_TABLE 
         WHERE  "timestamp" >= TIMESTAMP '2016-03-01 00:00:00'
         GROUP BY
                TO_CHAR( "timestamp", 'mm/dd/yyyy' ),
                EXTRACT( HOUR FROM "timestamp" )
       ) m
       PARTITION BY ( m.day, m.hr )
       ON ( m.hr = h.hr );
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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
Improve this answer

7 Comments

looks like he will probably need the date component too though given he has multiple days represented Otherwise how will he know that on 3/2/2016 he was missing 9 AM but on 3/3 that was 6 PM
@vkp, Thanks for the reply I tried that but I am still getting the same result
I am getting an error on line 8 m.*. The error is: invalid user.table.column, table.column, or column specification
i see..change it to count(m.somecolumnname) ..i edited the answer as well
Thanks, your answer is working perfectly. However, I am seeing results from 2011. Where do I add this line of code where timestamp >= to_timestamp('03/01/2016','MM/DD/YYYY') so that I only see results after a certain date?
|
1 
with cteHours(h) as (select 0 from dual
                 union all 
                 select h+1 from cteHours where h < 24)

, cteDates(d) AS (
    SELECT
       trunc(MIN(timestamp)) as d
    FROM
       My_Table
    WHERE
       timestamp >= to_timestamp('03/01/2016','MM/DD/YYYY')

    UNION ALL

    SELECT
       d + 1 as d
    FROM
       cteDates
    WHERE
       d + 1 <= (SELECT trunc(MAX(timestamp)) FROM MY_TABLE)
)

, datesNumsCross (d,h) AS (
       SELECT
          d, h
       FROM
          cteDates 
          CROSS JOIN cteHours
)

select count(*), to_char(d.d, 'MM/DD/YYYY'), d.h
from datesNumsCross d
LEFT JOIN MY_TABLE m
ON d.d = trunc(m.timestamp)
AND d.h = to_char(m.timestamp, 'HH24')
group by d.d, d.h

@VPK is doing a good job at answering, I just happened to be writing this at the same time as his last edit to generate a date hour cross join. This solution differs from his in that it will get all dates between your desired max and min. Where as his will get only the dates within the table so if you have a day missing completely it would not be represented in his but would in this one. Plus I did a little clean up on the joins.

Improve this answer

Comments

1

Here is one way to do that.

Using Oracle's hierarchical query feature and level psuedo column, generate the dates and hours. Then do an outer join of above with your data. Need to adjust the value of level depending upon your desired range (This example uses 120). Start date needs to be set as well. It is ( trunc(sysdate, 'hh24')-2/24 ) in this example.

select nvl(c1.cnt, 0), d1.date_part, d1.hour_part
from
(
    select
      to_char(s.dt - (c.lev)/24, 'mm/dd/yyyy') date_part, 
      to_char(s.dt - (c.lev)/24, 'hh24') hour_part
    from
      (select level lev from dual connect by level <= 120) c,
      (select trunc(sysdate, 'hh24')-2/24 dt from dual) s
    where (s.dt - (c.lev)/24) < trunc(sysdate, 'hh24')-2/24
) d1
full outer join
(
    select 
        count(*) cnt, 
        to_char(timestamp, 'MM/DD/YYYY') date_part, 
        to_char(timestamp, 'HH24') hour_part
    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') 
) c1
on d1.date_part = c1.date_part
and d1.hour_part = c1.hour_part
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.