Results 1 to 6 of 6

Thread: oracle group by

  1. #1
    Join Date
    May 2003
    Posts
    47

    Unanswered: oracle group by

    TRAVEL CANCELLATION_DATE
    ------ -------------------------
    T12345
    T12352
    T12353
    T12350
    T12349
    T12345
    T12346 25-MAY-03
    T12347
    T12348 21-MAY-03
    T12349
    T12350 26-MAY-03
    T12351
    T12352
    T12353
    T12354
    T12353 26-MAY-03

    i want to get the no.of times a traveller has cancelled.
    for example
    for T12346 it must give me 1
    and T12353 it must give 1
    and T12351 it must give 0.

    kindly help me.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: oracle group by

    Originally posted by sundarr123123
    TRAVEL CANCELLATION_DATE
    ------ -------------------------
    T12345
    T12352
    T12353
    T12350
    T12349
    T12345
    T12346 25-MAY-03
    T12347
    T12348 21-MAY-03
    T12349
    T12350 26-MAY-03
    T12351
    T12352
    T12353
    T12354
    T12353 26-MAY-03

    i want to get the no.of times a traveller has cancelled.
    for example
    for T12346 it must give me 1
    and T12353 it must give 1
    and T12351 it must give 0.

    kindly help me.
    select traveller, count(distinct cancellation_date)
    from table
    group by traveller;

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Does that work if a traveler has two cancelations on the same date?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Marcus_A
    Does that work if a traveler has two cancelations on the same date?
    No - unless the time is recorded in the date column, and the two cancellations were made at different times.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    How about something like this (but syntax may be slightly off):

    select travel, count(*)
    from table
    where cancellation_date not null
    group by travel
    having count(*) > 0

    union all

    select a.travel, '0'
    from table a
    where not exists
    (select b.travel
    from table b
    where
    a.travel = b.travel and b.cancelation_date not null)

    order by ...
    Last edited by Marcus_A; 06-02-03 at 08:06.

  6. #6
    Join Date
    Jun 2003
    Posts
    34

    Group BY

    I would recommend :

    select traveller, count(cancellation_date)
    from table
    group by traveller;

    This will give the ount of all the dates for the same traveller even if the traveller has 2 cancellations in a day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •