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