CODEWITHSUNDEEP
sqloracle-databasegroup-by
Hassaan Rabbani
Hassaan Rabbani

Reputation: 2465

Oracle group by minute

I have written a query to get the data received with respect to every hour in a day. 

SELECT To_CHAR( A.req_start_time , 'DD/MM/YYYY HH24') as input , count(A.REQUEST_ID)
FROM ILBULK.SAS_RE_TASK_MESSAGE A,ILBULK.SAS_RE_REQUEST_MESSAGE
WHERE A.NE_TYPE = 'HLR'
LIKE '%Synchronous%'
AND A.REQUEST_ID = ILBULK.SAS_RE_REQUEST_MESSAGE.REQUEST_ID and A.REQ_START_TIME > to_DATE ('12/26/2014 00', 'MM/DD/YYYY HH24') and A.REQ_START_TIME < to_DATE('12/27/2014 00', 'MM/DD/YYYY HH24')
GROUP BY To_CHAR(A.REQ_START_TIME, 'DD/MM/YYYY HH24');

And I am getting following response

26/12/2014 02   13823
26/12/2014 14   4681
26/12/2014 12   2939
26/12/2014 18   457
26/12/2014 03   34327
26/12/2014 04   15673
26/12/2014 19   28885
26/12/2014 06   70699
26/12/2014 10   10743

Now i want to get data with respect to every minute ordered in ascending order, I have tried to split the hours but nothings working. How do I do that?

Upvotes: 3

Views: 18225

Answers (3)

Hassaan Rabbani
Hassaan Rabbani

Reputation: 2465

I have figured it out myself. :) Here is the new query

SELECT To_CHAR( A.req_start_time , 'HH24:MI:SS') as input , count(A.REQUEST_ID)
FROM ILBULK.SAS_RE_TASK_MESSAGE A,ILBULK.SAS_RE_REQUEST_MESSAGE
WHERE A.NE_TYPE = 'HLR' 
--and ILBULK.SAS_RE_REQUEST_MESSAGE.PROTOCOL LIKE '%SAS%' --and ILINK.SAS_RE_REQUEST_MESSAGE.PROTOCOL LIKE '%Synchronous%'
AND A.REQUEST_ID = ILBULK.SAS_RE_REQUEST_MESSAGE.REQUEST_ID and A.REQ_START_TIME > to_DATE ('12/26/2014 00', 'MM/DD/YYYY HH24') and A.REQ_START_TIME < to_DATE('12/27/2014 00', 'MM/DD/YYYY HH24')
GROUP BY To_CHAR(A.REQ_START_TIME, 'HH24:MI:SS');

Upvotes: -1

Flic
Flic

Reputation: 751

Your query doesn't appear to be including minutes in any of the date/time strings. You could try the following pattern:

'DD/MM/YYYY HH24:MI' - where MI represents minutes

For ordering purposes, you will need to convert your input field back to a date:

ORDER BY to_date(input, 'DD/MM/YYYY HH24:MI')

If you use the date pattern YYYY/MM/DD HH24:MI, conversion wouldn't be necessary as the Strings would come out in the same order as date/time. In Oracle, ascending sort is assumed but you can add ASC at the end for clarity if you prefer.

If you are after minutes since midnight, you will need something like this:

TO_NUMBER(TO_CHAR(input, 'SSSSS'))/60

In the above, SSSSS represents seconds since midnight - divide by 60 for minutes.

Upvotes: 2

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

Use to_char(A.req_start_time , 'DD/MM/YYYY HH24:MI'), where MI is the minutes part. Add the same to the SELECT as well as GROUP BY clause.

SELECT To_char(A.req_start_time, 'DD/MM/YYYY HH24:MI') AS input, 
       Count(A.request_id) 
FROM   ilbulk.sas_re_task_message A, 
       ilbulk.sas_re_request_message 
WHERE  A.ne_type = 'HLR' LIKE '%Synchronous%' 
       AND A.request_id = ilbulk.sas_re_request_message.request_id 
       AND A.req_start_time > To_date ('12/26/2014 00', 'MM/DD/YYYY HH24') 
       AND A.req_start_time < To_date('12/27/2014 00', 'MM/DD/YYYY HH24') 
GROUP  BY To_char(A.req_start_time, 'DD/MM/YYYY HH24:MI');

For example,

SQL> SELECT to_char(SYSDATE, 'DD/MM/YYYY HH24:MI') GRP_MIN
  2  FROM DUAL
  3  GROUP BY to_char(SYSDATE, 'DD/MM/YYYY HH24:MI')
  4  /

GRP_MIN
----------------
29/12/2014 11:50

SQL>

Upvotes: 7

Related Questions