FAQ
I am writing a query that is grouping by 1 hour blocks over a period of time as follows

I am pretty sure the answer involves using "where not exists", but I can't get the dates I want to return.

select to_char(mydate, 'yyyymmdd hh24'), count(*)
from mytab
where mydate < sysdate
and mydate > to_date('20071125 1500', 'yyyymmdd hh24mi')

and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')
group by to_char(mydate, 'yyyymmdd hh24')
order by to_char(mydate, 'yyyymmdd hh24') desc

Now I have one hour periods that do not have any rows. A standard group by just ignores those periods. I want periods with no data to return and have a count(*) = 0

so I would have

2007111101 20
2007111102 0
2007111103 10

now it returns as

2007111101 20
2007111103 10

Search Discussions

  • Taylor, Chris David at Nov 27, 2007 at 3:47 pm
    Unless I'm mistaken, a group by will not ignore those rows especially
    when using a count(*).

    Either something else is disqualifying those rows from returning, or you
    might have a corrupt index if it is doing an index scan.

    Also, your results don't match your query. Looks like you're looking
    for 11/11/2007 dates but you're limiting it to dates > 11/25/2007. I
    assume this was just an oversight.

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-354-4799
    Email: chris.taylor_at_ingrambarge.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    ryan_gaffuri_at_comcast.net
    Sent: Tuesday, November 27, 2007 9:31 AM
    To: oracle-l_at_freelists.org
    Subject: tricky group by questions

    I am writing a query that is grouping by 1 hour blocks over a period of
    time as follows

    I am pretty sure the answer involves using "where not exists", but I
    can't get the dates I want to return.

    select to_char(mydate, 'yyyymmdd hh24'), count(*)
    from mytab
    where mydate < sysdate
    and mydate > to_date('20071125 1500', 'yyyymmdd hh24mi')

    and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')
    group by to_char(mydate, 'yyyymmdd hh24')
    order by to_char(mydate, 'yyyymmdd hh24') desc

    Now I have one hour periods that do not have any rows. A standard group
    by just ignores those periods. I want periods with no data to return and
    have a count(*) = 0

    so I would have

    2007111101 20
    2007111102 0
    2007111103 10

    now it returns as

    2007111101 20
    2007111103 10
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Taylor, Chris David at Nov 27, 2007 at 3:57 pm

    Also your query is doing:

    to_date('20071125 1500', 'yyyymmdd hh24mi')
    AND

    < to_date('20071125 1600', 'yyyymmdd hh24mi')

    If your intervals in your table are 1 hour intervals, this will never
    return any data (assuming your 1 hour intervals occur on the hour).

    Now if you were using a 'BETWEEN to_date(xx) and to_date(yy)' then it
    would include both 1500 and 1600. But your excluding them with a '>'.
    You could of course do a '>=' and a '<=' to include the 1500 and 1600.

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-354-4799
    Email: chris.taylor_at_ingrambarge.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Taylor, Chris David
    Sent: Tuesday, November 27, 2007 9:47 AM
    To: ryan_gaffuri_at_comcast.net; oracle-l_at_freelists.org
    Subject: RE: tricky group by questions

    Unless I'm mistaken, a group by will not ignore those rows especially
    when using a count(*).

    Either something else is disqualifying those rows from returning, or you
    might have a corrupt index if it is doing an index scan.

    Also, your results don't match your query. Looks like you're looking
    for 11/11/2007 dates but you're limiting it to dates > 11/25/2007. I
    assume this was just an oversight.

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-354-4799
    Email: chris.taylor_at_ingrambarge.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    ryan_gaffuri_at_comcast.net
    Sent: Tuesday, November 27, 2007 9:31 AM
    To: oracle-l_at_freelists.org
    Subject: tricky group by questions

    I am writing a query that is grouping by 1 hour blocks over a period of
    time as follows

    I am pretty sure the answer involves using "where not exists", but I
    can't get the dates I want to return.

    select to_char(mydate, 'yyyymmdd hh24'), count(*)
    from mytab
    where mydate < sysdate
    and mydate > to_date('20071125 1500', 'yyyymmdd hh24mi')

    and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')
    group by to_char(mydate, 'yyyymmdd hh24')
    order by to_char(mydate, 'yyyymmdd hh24') desc

    Now I have one hour periods that do not have any rows. A standard group
    by just ignores those periods. I want periods with no data to return and
    have a count(*) = 0

    so I would have

    2007111101 20
    2007111102 0
    2007111103 10

    now it returns as

    2007111101 20
    2007111103 10

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Nov 27, 2007 at 4:28 pm
    Ryan,

    Here's a nice AskTom example, which I think will do exactly what you want:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8912311513313

    --
    Mark J. Bobak
    Senior Database Administrator, System & Product Technologies
    ProQuest
    789 E. Eisenhower, Parkway, P.O. Box 1346
    Ann Arbor MI 48106-1346
    +1.734.997.4059  or +1.800.521.0600 x 4059
    mark.bobak_at_il.proquest.com
    www.proquest.com
    www.csa.com

    ProQuest...Start here.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of ryan_gaffuri_at_comcast.net
    Sent: Tuesday, November 27, 2007 10:31 AM
    To: oracle-l_at_freelists.org
    Subject: tricky group by questions

    I am writing a query that is grouping by 1 hour blocks over a period of time as follows

    I am pretty sure the answer involves using "where not exists", but I can't get the dates I want to return.

    select to_char(mydate, 'yyyymmdd hh24'), count(*)
    from mytab
    where mydate < sysdate
    and mydate > to_date('20071125 1500', 'yyyymmdd hh24mi')

    and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')
    group by to_char(mydate, 'yyyymmdd hh24')
    order by to_char(mydate, 'yyyymmdd hh24') desc

    Now I have one hour periods that do not have any rows. A standard group by just ignores those periods. I want periods with no data to return and have a count(*) = 0

    so I would have

    2007111101 20
    2007111102 0
    2007111103 10

    now it returns as

    2007111101 20
    2007111103 10
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Nov 27, 2007 at 10:36 pm
    While that is indeed a very nice example and is probably exactly on point as
    a solution except for the bit about rownum <= 12, if you're building a
    production service it probably makes sense to build a permanent "spine"
    table against which to perform the outer join to enumerate the times with
    zero rows of actual data. In this case the spine table would simply be the
    one column table of times by hour certain to bound your actual data. I
    recommend you create that table with the data values in order by time, and
    if you need more speed you can test whether it is better with an index, as
    an index organized table, or as a hash cluster in the context of your
    dataset and date range. I predict it would perform best if the "spine" table
    is presented to the query as a virtual table with its bounds and a
    solicitation of rownum so that it will be projected first and statically.
    Then if nested loops is chosen by the optimizer it will be a single pass and
    the sort will be a no-op. This of course is a trade-off of a little space
    (miniscule, I'd guess) versus CPU at run time, and that is my presumptive
    bias unless I know the resources headroom in an actual case is contrary to
    that bias.

    I predict such a solution will be faster, but I'm pretty doggone sure it
    will be more readable. Using all_objects is a nice source of rows usually in
    sufficient quantity (yet not enormous) for examples, but it is probably not
    a good idea to repetitively query it in production. More importantly, I
    wouldn't want someone wondering what all_objects has to do with the purpose
    of the script. In examples, we're queued up to know that all_objects is just
    a convenient stand-in for a row source, and we don't mind burning the cpu to
    fashion whatever values we need for the example, but I would suggest that an
    object for the purpose in this case will serve you better.

    You also probably need a little attention to your greater-than less-than
    values to get exactly the results you intend.

    Regards,

    mwf

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Bobak, Mark
    Sent: Tuesday, November 27, 2007 11:29 AM
    To: ryan_gaffuri_at_comcast.net; oracle-l_at_freelists.org
    Subject: RE: tricky group by questions

    Ryan,

    Here's a nice AskTom example, which I think will do exactly what you want:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:89123115
    13313

    --
    Mark J. Bobak
    Senior Database Administrator, System & Product Technologies
    ProQuest
    789 E. Eisenhower, Parkway, P.O. Box 1346
    Ann Arbor MI 48106-1346
    +1.734.997.4059  or +1.800.521.0600 x 4059
    mark.bobak_at_il.proquest.com
    www.proquest.com
    www.csa.com

    ProQuest...Start here.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of ryan_gaffuri_at_comcast.net
    Sent: Tuesday, November 27, 2007 10:31 AM
    To: oracle-l_at_freelists.org
    Subject: tricky group by questions

    I am writing a query that is grouping by 1 hour blocks over a period of time
    as follows

    I am pretty sure the answer involves using "where not exists", but I can't
    get the dates I want to return.

    select to_char(mydate, 'yyyymmdd hh24'), count(*)
    from mytab
    where mydate < sysdate
    and mydate > to_date('20071125 1500', 'yyyymmdd hh24mi')

    and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')
    group by to_char(mydate, 'yyyymmdd hh24')
    order by to_char(mydate, 'yyyymmdd hh24') desc

    Now I have one hour periods that do not have any rows. A standard group by
    just ignores those periods. I want periods with no data to return and have a
    count(*) = 0

    so I would have

    2007111101 20
    2007111102 0
    2007111103 10

    now it returns as

    2007111101 20
    2007111103 10
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Boyle, Christopher at Nov 27, 2007 at 4:34 pm
    Would something like this be feasible?

    select select to_char(mydate, 'yyyymmdd hh24'), count (*) over
    (partition by to_char(sysdate, 'YYYYMMDD HH24'))
    from mytab
    where mydate > to_date('20071125 0000', 'yyyymmdd hh24mi')

    and mydatedate < to_date('20071125 2400', 'yyyymmdd hh24mi')
    group by to_char(mydate, 'yyyymmdd hh24')
    order by to_char(mydate, 'yyyymmdd hh24') desc

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    ryan_gaffuri_at_comcast.net
    Sent: Tuesday, November 27, 2007 10:31 AM
    To: oracle-l_at_freelists.org
    Subject: tricky group by questions

    I am writing a query that is grouping by 1 hour blocks over a period of
    time as follows

    I am pretty sure the answer involves using "where not exists", but I
    can't get the dates I want to return.

    select to_char(mydate, 'yyyymmdd hh24'), count(*)
    from mytab
    where mydate < sysdate
    and mydate > to_date('20071125 1500', 'yyyymmdd hh24mi')

    and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')
    group by to_char(mydate, 'yyyymmdd hh24')
    order by to_char(mydate, 'yyyymmdd hh24') desc

    Now I have one hour periods that do not have any rows. A standard group
    by just ignores those periods. I want periods with no data to return and
    have a count(*) = 0

    so I would have

    2007111101 20
    2007111102 0
    2007111103 10

    now it returns as

    2007111101 20
    2007111103 10

    --
    http://www.freelists.org/webpage/oracle-l

    ______________________________________________________________________
    This email has been scanned by the MessageLabs Email Security System.
    For more information please visit http://www.messagelabs.com/email
    ______________________________________________________________________

    NOTICE OF CONFIDENTIALITY: Information included in and/or attached to this electronic mail transmission may be confidential. This electronic mail transmission is intended for the addressee(s) only. Any unauthorized disclosure, reproduction, or distribution of, and/or any unauthorized action taken in reliance on the information in this electronic mail is prohibited. If you believe that you have received this electronic mail transmission in error, please notify the sender by reply transmission, or contact helpdesk_at_multiplan.com, and delete the message without copying or disclosing it.

    ______________________________________________________________________
    This email has been scanned by the MessageLabs Email Security System.
    For more information please visit http://www.messagelabs.com/email
    ______________________________________________________________________
    --
    http://www.freelists.org/webpage/oracle-l
  • Job Miller at Nov 27, 2007 at 4:44 pm
    Are you looking for partition outer join functionality?

    http://www.oracle-developer.net/display.php?id=312

    If you don't have 10g, the above article talks about how to simulate partition outer join as well with inline views.

    Original Message ----
    From: "Taylor, Chris David"
    To: ryan_gaffuri_at_comcast.net; oracle-l_at_freelists.org
    Sent: Tuesday, November 27, 2007 10:47:08 AM
    Subject: RE: tricky group by questions

    Unless I'm mistaken, a group by will not ignore those rows especially
    when using a count(*).

    Either something else is disqualifying those rows from returning, or
    you
    might have a corrupt index if it is doing an index scan.

    Also, your results don't match your query. Looks like you're looking
    for 11/11/2007 dates but you're limiting it to dates > 11/25/2007. I
    assume this was just an oversight.

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-354-4799
    Email: chris.taylor_at_ingrambarge.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    ryan_gaffuri_at_comcast.net
    Sent: Tuesday, November 27, 2007 9:31 AM
    To: oracle-l_at_freelists.org
    Subject: tricky group by questions

    I am writing a query that is grouping by 1 hour blocks over a period of
    time as follows

    I am pretty sure the answer involves using "where not exists", but I
    can't get the dates I want to return.

    select to_char(mydate, 'yyyymmdd hh24'), count(*)
    from mytab
    where mydate < sysdate
    and mydate > to_date('20071125 1500', 'yyyymmdd hh24mi')

    and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')
    group by to_char(mydate, 'yyyymmdd hh24')
    order by to_char(mydate, 'yyyymmdd hh24') desc

    Now I have one hour periods that do not have any rows. A standard group
    by just ignores those periods. I want periods with no data to return
    and
    have a count(*) = 0

    so I would have

    2007111101 20
    2007111102 0
    2007111103 10

    now it returns as

    2007111101 20
    2007111103 10
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

    Be a better pen pal.
    Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
    --
    http://www.freelists.org/webpage/oracle-l
  • Mercadante, Thomas F (LABOR) at Nov 27, 2007 at 4:44 pm
    Ryan,

    This is a pretty stupid way to do this, but it works. You are not
    getting counts in your query because there are no rows to count. The
    query below creates 24 rows (1 per hour) to force Oracle to give you a
    row. Union that result set with your query, sum the results and
    subtract 1 and you get your result.

    Tom

    select distinct sub.hour, sum(sub.count)-1
    from (
    select to_number(to_char(mydate,'hh')) hour, count(*) count
    from mytab
    group by to_char(mydate,'hh')
    union
    select rownum,count(*)
    from all_objects
    where rownum < 25
    group by rownum
    ) sub
    group by sub.hour


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    ryan_gaffuri_at_comcast.net
    Sent: Tuesday, November 27, 2007 10:31 AM
    To: oracle-l_at_freelists.org
    Subject: tricky group by questions

    I am writing a query that is grouping by 1 hour blocks over a period of
    time as follows

    I am pretty sure the answer involves using "where not exists", but I
    can't get the dates I want to return.

    select to_char(mydate, 'yyyymmdd hh24'), count(*) from mytab where
    mydate < sysdate and mydate > to_date('20071125 1500', 'yyyymmdd
    hh24mi') and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')
    group by to_char(mydate, 'yyyymmdd hh24') order by to_char(mydate,
    'yyyymmdd hh24') desc

    Now I have one hour periods that do not have any rows. A standard group
    by just ignores those periods. I want periods with no data to return and
    have a count(*) = 0

    so I would have

    2007111101 20
    2007111102 0
    2007111103 10

    now it returns as

    2007111101 20
    2007111103 10
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Taylor, Chris David at Nov 27, 2007 at 4:58 pm
    Ahhh...There is no row in there for that interval, therefore it wouldn't
    return a 0.....duhhhhhh. My fault on my earlier replies...

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-354-4799
    Email: chris.taylor_at_ingrambarge.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Mercadante, Thomas F
    (LABOR)

    Sent: Tuesday, November 27, 2007 10:45 AM
    To: ryan_gaffuri_at_comcast.net; oracle-l_at_freelists.org
    Subject: RE: tricky group by questions

    Ryan,

    This is a pretty stupid way to do this, but it works. You are not
    getting counts in your query because there are no rows to count. The
    query below creates 24 rows (1 per hour) to force Oracle to give you a
    row. Union that result set with your query, sum the results and
    subtract 1 and you get your result.

    Tom

    select distinct sub.hour, sum(sub.count)-1
    from (
    select to_number(to_char(mydate,'hh')) hour, count(*) count
    from mytab
    group by to_char(mydate,'hh')
    union
    select rownum,count(*)
    from all_objects
    where rownum < 25
    group by rownum
    ) sub
    group by sub.hour


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    ryan_gaffuri_at_comcast.net
    Sent: Tuesday, November 27, 2007 10:31 AM
    To: oracle-l_at_freelists.org
    Subject: tricky group by questions

    I am writing a query that is grouping by 1 hour blocks over a period of
    time as follows

    I am pretty sure the answer involves using "where not exists", but I
    can't get the dates I want to return.

    select to_char(mydate, 'yyyymmdd hh24'), count(*) from mytab where
    mydate < sysdate and mydate > to_date('20071125 1500', 'yyyymmdd
    hh24mi') and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')
    group by to_char(mydate, 'yyyymmdd hh24') order by to_char(mydate,
    'yyyymmdd hh24') desc

    Now I have one hour periods that do not have any rows. A standard group
    by just ignores those periods. I want periods with no data to return and
    have a count(*) = 0

    so I would have

    2007111101 20
    2007111102 0
    2007111103 10

    now it returns as

    2007111101 20
    2007111103 10
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Stephane Faroult at Nov 27, 2007 at 4:45 pm
    Ryan,
    I'd rather run something like
    select ref.ref_time, coalesce(x.cnt, 0)
    from   (select suitably_formatted(your_min_date + rownum / 24) ref_time
    from dual
    connect by level <= ceil((your_max_date - your_min_date) * 24)) ref,
    left outer join (your_query_here - rename count(*) as cnt) x
    on x.time = ref.ref_time
    HTH
    SF

    On Die Nov 27 16:31, ryan_gaffuri@comcast.net sent:

    I am writing a query that is grouping by 1 hour blocks over a period of time as follows

    I am pretty sure the answer involves using "where not exists", but I can't get the dates I want to return.

    select to_char(mydate, 'yyyymmdd hh24'), count(*)
    from mytab
    where mydate < sysdate
    and mydate > to_date('20071125 1500', 'yyyymmdd hh24mi')
    and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')
    group by to_char(mydate, 'yyyymmdd hh24')
    order by to_char(mydate, 'yyyymmdd hh24') desc

    Now I have one hour periods that do not have any rows. A standard group by just ignores those periods. I want periods with no data to return and have a count(*) = 0

    so I would have

    2007111101 20
    2007111102 0
    2007111103 10

    now it returns as

    2007111101 20
    2007111103 10
  • Ken Naim at Nov 27, 2007 at 5:06 pm
    You'll have to create a master list of all the hours you want and then do an
    outer join to the data that you are counting with an nvl around the count.

    Here is an example, I am not connected to the database right now so I can't
    test it for syntax errors.





    Select to_char(allhours, 'yyyymmdd hh24'), nvl(count(b.mydate),0)

    from (select to_date('20071125 1500', 'yyyymmdd hh24mi') + rownum/24
    allhours

    from mytab -- or any small table with enough rows to meet the number
    of hours required

    where to_date('20071125 1500', 'yyyymmdd hh24mi') + rownum/24 >
    to_date('20071125 1500', 'yyyymmdd hh24mi')

    and to_date('20071125 1500', 'yyyymmdd hh24mi') + rownum/24 <
    sysdate) a,

    mytab b

    where b.mydate(+) between a.allhours and a.allhours+3559/86400

    and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')

    group by to_char(allhours, 'yyyymmdd hh24')

    order by allhours desc





    Ken

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of ryan_gaffuri_at_comcast.net
    Sent: Tuesday, November 27, 2007 10:31 AM
    To: oracle-l_at_freelists.org
    Subject: tricky group by questions



    I am writing a query that is grouping by 1 hour blocks over a period of time
    as follows



    I am pretty sure the answer involves using "where not exists", but I can't
    get the dates I want to return.



    select to_char(mydate, 'yyyymmdd hh24'), count(*)

    from mytab

    where mydate < sysdate

    and mydate > to_date('20071125 1500', 'yyyymmdd hh24mi')

    and mydatedate < to_date('20071125 1600', 'yyyymmdd hh24mi')

    group by to_char(mydate, 'yyyymmdd hh24')

    order by to_char(mydate, 'yyyymmdd hh24') desc



    Now I have one hour periods that do not have any rows. A standard group by
    just ignores those periods. I want periods with no data to return and have a
    count(*) = 0



    so I would have



    2007111101 20

    2007111102 0

    2007111103 10



    now it returns as



    2007111101 20

    2007111103 10

    --

    http://www.freelists.org/webpage/oracle-l



    --
    http://www.freelists.org/webpage/oracle-l
  • Nigel Thomas at Nov 27, 2007 at 5:28 pm

    Ryan wrote:
    Now I have one hour periods that do not have any rows. A standard group by just ignores those >periods. I want periods with no data to return and have a count(*) = 0
    Unless you are joining to a table / view that defines the periods, you can't have the missing periods in the result of your group by. If they're not in the ungrouped data, how can they be in the aggregate?

    Of course it is easy to define a view / query to construct those periods you need on the fly - something like (no test system so apologies for typos):

    with period_list as (

    select to_date('20071125 1500', 'yyyymmdd hh24mi') --- YOUR START TIME HERE (as a date)
    + (rownum/24) hh24
    from all_objects
    where rownum < 2 --- THE NUMBER OF PERIODS YOU WANT

    )
    select to_char(period_list.hh24, 'yyyymmdd hh24'), count(*)
    from period_list
    left join mytab on trunc(mydate,'HH24') = period_list.hh24 --- JOIN REMOVES NEED FOR WHERE
    group by period_list.hh24
    order by period_list.hh24 desc

    YMMV - other ways of expressing the same concept may scale better, depending on the size of mytab and the number of periods involved...

    HTH

    Regards Nigel
  • Greg Rahn at Nov 27, 2007 at 10:46 pm

    On 11/27/07, ryan_gaffuri_at_comcast.net wrote:
    I am writing a query that is grouping by 1 hour blocks over a period of time as follows

    Now I have one hour periods that do not have any rows. A standard group by just ignores those periods.
    I want periods with no data to return and have a count(*) = 0
    A time dimension table is needed to do this. A logical table works
    well here, rather than physical.

    Here is one approach which should perform quite well since it should
    do the fact group by reduction before it makes the join to the time
    dimension as the time dimension is only required for pretty printing,
    not predicate filtering. Adjust as necessary.

    with f as (
    select

    to_char(create_date, 'yyyymmddhh24') time_id, count(*) cnt
    from

    fact_table
    where

    create_date > to_date('20071127 0000', 'yyyymmdd hh24mi') and
    create_date < to_date('20071127 1900', 'yyyymmdd hh24mi')
    group by
    to_char(create_date, 'yyyymmddhh24')

    ),
    d as (
    select

    to_char((to_date('20071127 0000', 'yyyymmdd hh24mi') + rownum /
    24),'yyyymmddhh24') time_id
    from

    dual
    connect by

    level <=
    (to_date('20071127 1900', 'yyyymmdd hh24mi') -
    to_date('20071127 0000', 'yyyymmdd hh24mi')) *24

    )
    select

    d.time_id, nvl(f.cnt,0) count
    from

    f, d
    where

    d.time_id = f.time_id(+)
    order by

    d.time_id desc
    /

    TIME_ID COUNT

    ---------- ----------

    2007112719 0
    2007112718 0
    2007112717 0
    2007112716 0
    2007112715 0
    2007112714 0
    2007112713 0
    2007112712 0
    2007112711 41
    2007112710 60
    2007112709 60
    2007112708 60
    2007112707 60
    2007112706 60
    2007112705 60
    2007112704 60
    2007112703 60
    2007112702 60
    2007112701 60

    19 rows selected.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 27, '07 at 3:31p
activeNov 27, '07 at 10:46p
posts13
users11
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase