Skip to Main Content

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!

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

fac586

Duplicate thread:

1 - 1
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,432 views