OraFAQ Forum: SQL & PL/SQL » Group by time duration

Home » SQL & PL/SQL » SQL & PL/SQL » Group by time duration

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Group by time duration [message #1999]

Sat, 15 June 2002 08:57

Puja
Messages: 2
Registered: June 2002

Junior Member

I am a newbie to oracle. I've grouped by a particular column but not time range.

I've a startdate and closeddate in a table task.

I've to select closeddate-startdate from task where closeddate - startdate lies in the time interval(0-15 mins,0-30 mins).

I've to group by time duration i.e 0-15 mins,15-30mins .

Immediate help would be appreciated.

Thanks,
Puja

Report message to a moderator

Re: Group by time duration [message #2000 is a reply to message #1999]

Sat, 15 June 2002 10:49

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

you group the dates like this.
change the code to substrate the dates and extract the minutes only

SQL> SELECT days, SUM(salary) FROM
  2  (SELECT DECODE(TRUNC((TO_CHAR(sal_date, 'dd')-1)/10),
  3                0, '01-10',
  4                1, '10-20',
  5                2, '21-31',
  6                   '21-31') days,
  7         salary
  8    FROM salaries)
  9  GROUP BY days;

DAYS  SUM(SALARY)
----- -----------
01-10         300
10-20         300
21-31         400

Report message to a moderator

Re: Group by time duration [message #2006 is a reply to message #1999]

Sun, 16 June 2002 06:22

Puja
Messages: 2
Registered: June 2002

Junior Member

Thanks for your immediate response.

I tried the following sql:
SELECT mins,((closeddate-startdate) *1440) as response,activitydesc,subactivitydesc FROM
(SELECT DECODE(((closeddate-startdate) *1440),
0, '01-10',
1, '10-20',
2, '30-40',
3,'40-50',
4, '50-60',
'60-100') mins,activitydesc,subactivitydesc,startdate,closeddate
from actionitem)
group by mins;

It gave me error "not a group by expression"

I tried without the groupby and got the output but everything falls under 60-100 range.

The output was:

mins response activitydesc subactivitydesc
60-100 10 need ac ac delivery
60-100 40 pickup valet
60-100 50 plumbing bathroom leakage

Whats wrong with the statement?

Thanks,
Puja

Report message to a moderator

Re: Group by time duration [message #2013 is a reply to message #2006]

Sun, 16 June 2002 23:34

lakshmi
Messages: 22
Registered: July 2000

Junior Member

try grouping by mins,activitydesc,subactivitydesc,startdate,closeddate.

Report message to a moderator

Previous Topic:	about entring the time
Next Topic:	Buffer overflow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 08:59:21 CDT 2024