4 Replies Latest reply: Feb 2, 2013 10:21 AM by Frank Kulash RSS

    group by based on time interval

    doubtsinora
      Hi,

      I have grouped the log count for every one hour for the past one month. No I wanted to find the minimum (Hour with minimum logs) for each day. Could you please help me out?

      The below query doesn't work:

      select to_char(HOURLY, 'MM/DD/YYYY HH24:MI:SS'), min(sms) from
      (select trunc(createdate,'hh24') + (trunc(to_char(createdate,'mi')/60)*60)/24/60 HOURLY, count(*) sms
      from testlog
      where createdate >= to_date('01/01/2013', 'MM/DD/YYYY')
      group by trunc(createdate,'hh24') + (trunc(to_char(createdate,'mi')/60)*60)/24/60)
      group by to_char(Hourly, 'MM/DD')

      I get ORA: 00979 error. Could some one help me out?
        • 1. Re: group by based on time interval
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Top-N Query
            Frank Kulash
            Hi,

            Welcome to the forum!
            985680 wrote:
            Hi,

            I have grouped the log count for every one hour for the past one month. No I wanted to find the minimum (Hour with minimum logs) for each day. Could you please help me out?

            The below query doesn't work:

            select to_char(HOURLY, 'MM/DD/YYYY HH24:MI:SS'), min(sms) from
            (select trunc(createdate,'hh24') + (trunc(to_char(createdate,'mi')/60)*60)/24/60 HOURLY, count(*) sms
            from testlog
            where createdate >= to_date('01/01/2013', 'MM/DD/YYYY')
            group by trunc(createdate,'hh24') + (trunc(to_char(createdate,'mi')/60)*60)/24/60)
            group by to_char(Hourly, 'MM/DD')

            I get ORA: 00979 error. Could some one help me out?
            Right. In the outer query, you're GROUPing BY
            to_char(Hourly, 'MM/DD') 
            but in the SELECT clause , you're trying to reference
            to_char(HOURLY, 'MM/DD/YYYY HH24:MI:SS')
            which is not the same as the GROUP BY expression, and does not depend on the GROUP BY expression.

            It sounds like you want something like this:
            WITH  hour_summary      AS
            (
                 SELECT       TRUNC (createdate, 'HH')     AS hour
                 ,       COUNT (*)                    AS hour_cnt
                 ,       RANK () OVER ( PARTITION BY  TRUNC (TRUNC (createdate, 'HH'))
                                               ORDER BY      COUNT (*)
                                  )             AS day_rank
                 FROM       testlog
                 WHERE       createdate     >= TO_DATE ( '01/01/2013'
                                             , 'MM/DD/YYYY'
                                        )
                 GROUP BY  TRUNC (createdate, 'HH') 
            )
            SELECT       hour
            ,       hour_cnt
            FROM       hour_summary
            WHERE       day_rank     = 1
            ORDER BY  hour
            ;
             

            I hope this answers your question.
            If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
            Explain, using specific examples, how you get those results from that data.
            Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
            See the forum FAQ {message:id=9360002}

            You'll get better replies sooner if you always include this information whenever you have a question.
            • 3. Re: Top-N Query
              doubtsinora
              Thanks a lot! This worked. I was wondering if this can be done without a rank function?

              Henceforth I will include all those information.

              Edited by: doubtsinora on Feb 2, 2013 7:36 AM
              • 4. Re: Top-N Query
                Frank Kulash
                Hi,
                doubtsinora wrote:
                ... I was wondering if this can be done without a rank function?
                Yes, there are several ways to do it without using RANK or any similar function, depending on your data and your requirements.

                Why you need to avoid RANK. What other useful functions or features will you want to avoid?
                What results do you want in case of a tie (that is, two or more hours having the same lowest count)?
                What version of Oracle are you using?