Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Complex select query help (Group by)

user12050217Oct 13 2009 — edited Dec 21 2009
Hi,
I am in need your help to create a single select query for below table data and output which requies. I am using oracle 10g on RHEL 5 version.

create table bustour(bussno number(4),start_date date,passengers number(2));
alter session set nls_date_format='dd-mm-yyyy';
insert into bustour values (4123,'04-10-2009',20);
insert into bustour values (4123,'05-10-2009',25);
insert into bustour values (4123,'05-10-2009',18);
insert into bustour values (4123,'06-10-2009',15);

insert into bustour values (6138,'03-10-2009',16);
insert into bustour values (6138,'03-10-2009',19);
insert into bustour values (6138,'04-10-2009',22);
insert into bustour values (6138,'04-10-2009',13);

insert into bustour values (4123,'07-10-2009',23);
insert into bustour values (4123,'08-10-2009',27);
insert into bustour values (4123,'11-10-2009',15);

insert into bustour values (6138,'05-10-2009',16);
insert into bustour values (6138,'05-10-2009',13);
insert into bustour values (6138,'05-10-2009',18);
insert into bustour values (6138,'05-10-2009',24);
insert into bustour values (6138,'07-10-2009',20);
insert into bustour values (6138,'08-10-2009',18);
SQL> select * from bustour;

    BUSSNO START_DATE PASSENGERS
---------- ---------- ----------
      4123 04-10-2009         20
      4123 05-10-2009         25
      4123 05-10-2009         18
      4123 06-10-2009         15
      6138 03-10-2009         16
      6138 03-10-2009         19
      6138 04-10-2009         22
      6138 04-10-2009         13
      4123 07-10-2009         23
      4123 08-10-2009         27
      4123 11-10-2009         15

    BUSSNO START_DATE PASSENGERS
---------- ---------- ----------
      6138 05-10-2009         16
      6138 05-10-2009         13
      6138 05-10-2009         18
      6138 05-10-2009         24
      6138 07-10-2009         20
      6138 08-10-2009         18

17 rows selected.

I want query output as below :

Bussno  start_date      end_Date   totalpassengers   maxpessenger  maxpassdate
4123    04-10-09        06-10-09          78              25         05-10-09
6138    03-10-09        04-10-09          70              22         04-10-09 
4123    07-10-09        11-10-09          65              27         08-10-09
6138    05-10-09        08-10-09         109              24         05-10-09
So, that we can know on which particular trip-schedule we got maximum passenger and date. (To calculate driver's % of bonus)

Please help me to write the query.

Best regards
Nisha
This post has been answered by Boneist on Oct 13 2009
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 18 2010
Added on Oct 13 2009
25 comments
2,359 views