Public User Public User - 4 months ago 7
SQL Question

oracle sql count(*) with rows not matched

I have a oracle query:

select to_char(te.HORA, 'hh24:mi') HORARIO, count(1) CANTIDAD
from db.t_error te
where (te.error LIKE 'ERR-108' or te.error LIKE 'ERR-256')
and te.HORA >= to_HORA('29-07-2016 18:50', 'dd-mm-yyyy hh24:mi')
and te.HORA <= to_HORA('29-07-2016 19:00', 'dd-mm-yyyy hh24:mi')
group by to_char(te.HORA, 'hh24:mi')
order by to_char(te.HORA, 'hh24:mi');


Result (Table):
HORARIO | CANTIDAD

18:53 2

18:56 2

18:58 1

18:59 1

but i need Result (table):

HORARIO | CANTIDAD

18:50 0

18:51 0

18:52 0

18:53 2

18:54 0

18:55 0

18:56 2

18:57 0

18:58 1

18:59 1

19:00 0

Count minute to minute with 0 values or not matched result for count(1).

I hope help!

Thanks.

Answer

This is not a good solution (it should be implemented to work directly with your code, not as an add-on), but it illustrates how this should be done.

to_date in this context adds the time (using the hh24:mi format model) to the first day of the current month, but you don't care about that since you extract the hour and minute and discard the rest anyway.

Biggest thing to fix in your code is that you work with strings. Best to put everything in date datatype, group by date datatype (to the minute), and only use to_char() for display purposes at the very end. Please write back if you need help with that.

with
     your_table ( horario, cantidad ) as (
       select '18:53', 2 from dual union all
       select '18:56', 2 from dual union all
       select '18:58', 1 from dual union all
       select '18:59', 1 from dual
     ),
     all_times ( horario ) as (
       select to_char( to_date('18:50', 'hh24:mi') + (level - 1) / (24 * 60), 'hh24:mi')
       from   dual
       connect by level <= 1 + (to_date('19:00', 'hh24:mi') - 
                                                 to_date('18:50', 'hh24:mi')) * 24 * 60
     )
select a.horario, nvl(y.cantidad, 0) as cantidad
from   all_times a left outer join your_table y
                   on a.horario = y.horario
order by horario
;

HORARIO   CANTIDAD
------- ----------
18:50            0
18:51            0
18:52            0
18:53            2
18:54            0
18:55            0
18:56            2
18:57            0
18:58            1
18:59            1
19:00            0

11 rows selected.

Edited:

For a "cheap" solution with no extra effort, you can plug your original query directly into the "your_table" factored subquery, like so (but I don't have your base tables, so I can't test).

with
     your_table ( horario, cantidad ) as (
       select   to_char(te.HORA, 'hh24:mi') HORARIO, count(1) CANTIDAD 
       from     db.t_error te
       where    (te.error LIKE 'ERR-108' or te.error LIKE 'ERR-256')
         and    te.HORA >= to_HORA('29-07-2016 18:50', 'dd-mm-yyyy hh24:mi')
         and    te.HORA <= to_HORA('29-07-2016 19:00', 'dd-mm-yyyy hh24:mi')
       group by to_char(te.HORA, 'hh24:mi')        
     ),
     all_times ( horario ) as (
       select to_char( to_date('18:50', 'hh24:mi') + (level - 1) / (24 * 60), 'hh24:mi')
       from   dual
       connect by level <= 1 + (to_date('19:00', 'hh24:mi') - 
                                                 to_date('18:50', 'hh24:mi')) * 24 * 60
     )
select a.horario, nvl(y.cantidad, 0) as cantidad
from   all_times a left outer join your_table y
                   on a.horario = y.horario
order by horario
;
Comments