Forum Stats

  • 3,839,017 Users
  • 2,262,438 Discussions
  • 7,900,836 Comments

Discussions

Complex select query help (Group by)

user12050217
user12050217 Member Posts: 927
edited Dec 21, 2009 6:44AM in SQL & PL/SQL
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

Best Answer

  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    edited Oct 13, 2009 10:53AM Answer ✓
    Ok, something like this:
    with my_tab as (select 1 id, 4123 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 20 passengers from dual union all
                    select 2 id, 4123 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 25 passengers from dual union all
                    select 3 id, 4123 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual union all
                    select 4 id, 4123 busno, to_date('06/10/2009', 'dd/mm/yyyy') start_date, 15 passengers from dual union all
                    select 5 id, 6138 busno, to_date('03/10/2009', 'dd/mm/yyyy') start_date, 16 passengers from dual union all
                    select 6 id, 6138 busno, to_date('03/10/2009', 'dd/mm/yyyy') start_date, 19 passengers from dual union all
                    select 7 id, 6138 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 22 passengers from dual union all
                    select 8 id, 6138 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 13 passengers from dual union all
                    select 9 id, 4123 busno, to_date('07/10/2009', 'dd/mm/yyyy') start_date, 23 passengers from dual union all
                    select 10 id, 4123 busno, to_date('08/10/2009', 'dd/mm/yyyy') start_date, 27 passengers from dual union all
                    select 11 id, 4123 busno, to_date('11/10/2009', 'dd/mm/yyyy') start_date, 15 passengers from dual union all
                    select 12 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 16 passengers from dual union all
                    select 13 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 13 passengers from dual union all
                    select 14 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual union all
                    select 15 id, 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 24 passengers from dual union all
                    select 16 id, 6138 busno, to_date('07/10/2009', 'dd/mm/yyyy') start_date, 20 passengers from dual union all
                    select 17 id, 6138 busno, to_date('08/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual)
    -- end of mimicking your data; use SQL below:
    select busno,
           min(start_date) start_date,
           max(start_date) end_date,
           sum(passengers) total_passengers,
           max(passengers) max_passengers,
           max_pass_date
    from   (select id,
                   start_date,
                   busno,
                   passengers,
                   distance,
                   max(start_date) keep (dense_rank first order by passengers desc) over (partition by distance, busno) max_pass_date
            from   (select id,
                           start_date,
                           busno,
                           passengers,
                           -- using tabibitosan method, borrowed from Aketi Jyuuzou 
                           dense_rank() over (order by id) -
                              row_number() over (partition by busno order by id) distance
                    from   my_tab))
    group by busno, distance, max_pass_date
    order by min(id)
    
         BUSNO START_DATE END_DATE   TOTAL_PASSENGERS MAX_PASSENGERS MAX_PASS_DATE
    ---------- ---------- ---------- ---------------- -------------- -------------
          4123 04/10/2009 06/10/2009               78             25 05/10/2009   
          6138 03/10/2009 04/10/2009               70             22 04/10/2009   
          4123 07/10/2009 11/10/2009               65             27 08/10/2009   
          6138 05/10/2009 08/10/2009              109             24 05/10/2009   
«13

Answers

  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    How are you determining the end date?
  • user12050217
    user12050217 Member Posts: 927
    Thanks. Upto the value of busno column remain same, because a complete bus trip data first entered and then another bus trip data.
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    Are there any other columns in this table that you are not providing? The reason I ask is because it seems as if your solution is dependent on when a particular item is inserted into the table. However, there is no column that indicates which items were inserted first such as a monotonically increasing value (a.k.a. sequence value). The order of the result set cannot be guaranteed without using an ORDER BY clause.

    Maybe I am missing something here but I cannot see an easy way to order the set initially to get the grouping you desire.

    Thanks!
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    user12050217 wrote:
    Thanks. Upto the value of busno column remain same, because a complete bus trip data first entered and then another bus trip data.
    Yes, but how are you determining the order in which the rows were inserted into the table?

    Think of a table like a pile of clothes on the floor - how can you tell the order with which each item of clothing was added to the pile if there isn't something that's keeping a note of when it was added to the pile? Answer: you can't.

    If your table doesn't have a column indicating the order the rows were inserted, then you're going to find it difficult to get the date you're after.
  • user12050217
    user12050217 Member Posts: 927
    No sir, there are any other column in the table, becasue we gets only bus number, no of passengers (tickets issued by bill book) and date of bus running by drivers; so we just feed the complete data of a particular bus. Now our manager has asked me to produce the above query result, so that they can further calculate the extra money for the drivers.

    Thanking you,
    Nisha
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    You'll need to go back to your managers and tell them that you won't be able to get the data they're after.

    However, it's a very strange requirement - "give me these random ranges determined by when they were inserted into the table" - if insert order is important, then I'd say the table needs a new column (timestamp) to hold the date + time the row was added plus a trigger to populate the column.

    I'd go back to your manager and ask what range of dates they'd be most interested in - weekly or monthly?
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Nisha,

    You must understand the simple fact that oracle does not have any predefined order for a table. So unless you use the ORDER BY clause the order in which the data are returned by oracle cannot be determined.

    Hence you must find a column which will determine the order in which the BUSSNO must be retrieved. If you cant find one then you are out of luck.
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    Eg. if they wanted the data weekly, then you'd do something like:
    with my_tab as (select 4123 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 20 passengers from dual union all
                    select 4123 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 25 passengers from dual union all
                    select 4123 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual union all
                    select 4123 busno, to_date('06/10/2009', 'dd/mm/yyyy') start_date, 15 passengers from dual union all
                    select 6138 busno, to_date('03/10/2009', 'dd/mm/yyyy') start_date, 16 passengers from dual union all
                    select 6138 busno, to_date('03/10/2009', 'dd/mm/yyyy') start_date, 19 passengers from dual union all
                    select 6138 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 22 passengers from dual union all
                    select 6138 busno, to_date('04/10/2009', 'dd/mm/yyyy') start_date, 13 passengers from dual union all
                    select 4123 busno, to_date('07/10/2009', 'dd/mm/yyyy') start_date, 23 passengers from dual union all
                    select 4123 busno, to_date('08/10/2009', 'dd/mm/yyyy') start_date, 27 passengers from dual union all
                    select 4123 busno, to_date('11/10/2009', 'dd/mm/yyyy') start_date, 15 passengers from dual union all
                    select 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 16 passengers from dual union all
                    select 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 13 passengers from dual union all
                    select 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual union all
                    select 6138 busno, to_date('05/10/2009', 'dd/mm/yyyy') start_date, 24 passengers from dual union all
                    select 6138 busno, to_date('07/10/2009', 'dd/mm/yyyy') start_date, 20 passengers from dual union all
                    select 6138 busno, to_date('08/10/2009', 'dd/mm/yyyy') start_date, 18 passengers from dual)
    -- end of mimicking your data; use SQL below:
    select busno,
           trunc(start_date, 'iw') start_date,
           trunc(start_date, 'iw') + 7 end_date,
           sum(passengers) total_passengers,
           max(passengers) max_passengers
    from   my_tab
    group by busno, trunc(start_date, 'iw')
    order by trunc(start_date, 'iw'), busno;
    
         BUSNO START_DATE END_DATE  TOTAL_PASSENGERS MAX_PASSENGERS
    ---------- ---------- --------- ---------------- --------------
          4123 28/SEP/09  05/OCT/09               20             20
          6138 28/SEP/09  05/OCT/09               70             22
          4123 05/OCT/09  12/OCT/09              123             27
          6138 05/OCT/09  12/OCT/09              109             24
    Boneist
  • user12050217
    user12050217 Member Posts: 927
    I would like to thanks to all for your fast and valuable replies. Here issue is how to order by; so i am assuming that order by rownum can be work; because once entered data, it will never updated and as we insert the rows, i hope it will be inserted at the bottom (free space in the extent). But if it is still require the change the table structure i.e. add a new column, i am ready to add the new column but please guide me in more details.

    Thanks to all you once again.
    Nisha
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    ROWNUM is a pseudocolumn which is generated when a row is outputted. So ordering by ROWNUM does not make any sense.

    Your best possible option is to have a timestamp column that will the capture the inserted time and you can just order by that column.
This discussion has been closed.