CODEWITHSUNDEEP
sql
Prince
Prince

Reputation: 13

Group by Hour, 0 if no data

I have the following format query:

select status as status,
    count(*) as count,
    EXTRACT(
        hour
        from creationtime
    ) AS hour,
    creationtime::date as datee
from user_logging_table_detail
where creationtime::date = current_date
    and status = 'SUCCESS'
group by hour,
    creationtime::date,
    status
order by hour asc


status  count   hour    datee
SUCCESS  1  1   8/6/2020
SUCCESS  2  2   8/6/2020
SUCCESS 5   3   8/6/2020
SUCCESS 2   4   8/6/2020
SUCCESS 3   5   8/6/2020
SUCCESS 2   8   8/6/2020
The desired output should be all the hours

    status  count   hour    datee 
    SUCCESS  1  1   8/6/2020
    SUCCESS  2  2   8/6/2020
    SUCCESS 5   3   8/6/2020
    SUCCESS 2   4   8/6/2020
    SUCCESS 3   5   8/6/2020
    SUCCESS 0   6   8/6/2020
    SUCCESS 0   7   8/6/2020
    SUCCESS 2   8   8/6/2020

i mean hour (6,7) shoud came with (0) value.

Upvotes: 1

Views: 226

Answers (2)

Lars Skaug
Lars Skaug

Reputation: 1386

Gordon Linoff's solution is elegant, but I thought I'd provide an generic alternative that is not dependent on a specific Postgres feature:

WITH t AS
(
    SELECT m * 10 + n h
    FROM (VALUES (0), (1), (2)) v1(m)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(n)
    where m * 10 + n < 25
)
select t.h, status as status,
    count(*) as count,
    EXTRACT(
        hour
        from creationtime
    ) AS hour,
    creationtime::date as datee
from t
 left join user_logging_table_detail
  on EXTRACT(hour from creationtime ) = t.h
   and creationtime::date = current_date
   and status = 'SUCCESS'
group by hour,
    creationtime::date,
    status
order by t.h;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Your syntax looks like Postgres, which has the convenient generate_series():

select gs.hh, ultd.status, count(ultd.status) as count
from generate_series(current_date, now(), interval '1 hour') gs(hh) left join
     zainksa_mobileapp.user_logging_table_detail ultd
     on ultd.creationtime >= gs.hh and
        ultd.creationtime < gs.hh + interval '1 hour' and
        ultd.status = 'SUCCESS' 
group by gs.hh, ultd.status
order by hour asc

Upvotes: 2

Related Questions