Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    4

    Unhappy Unanswered: Newbie need help with SQL

    Hi everyone,
    I'm a real newbie to this whole SQL/Oracle thing and although I've tried searching for the answers myself most of the time I find that I don't actually know what to look for. Unless I already know that a function exists to do what it is that I want to do, I am pretty much in the dark as to what to search for.

    I have a table that stores the details of all flights. The flight schedule is stored in a Schedule column in the following manner : '1234567' and 'Daily'. If the value is daily it means there is a flight every day. If the value is '1' then there is a flight on Monday, '2' means there is a flight on Tuesday, and so on.
    So there are various possible combinations, depending on the days which that flight is available.

    The problem is, I am trying to extract the day information from the table, as in trying to find out the number of flights that are available daily based on the values in the Schedule column and displaying the day as well as the number of flights on that day like this:

    Schedule : Total_Flights
    Monday 34
    Tuesday 45
    ...
    ..
    sunday 66

    Can anyone point me in the right direction?
    Thanks.

    ashazi

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Code:
    SQL> select * from a;
    
    SCH
    ----------
    1
    2
    3
    4
    5
    6
    7
    12
    23
    1234567
    
    10 rows selected.
    
    SQL> select sum(MON) MON,
      2  sum(TUE) TUE,
      3  sum(WED) WED,
      4  sum(THU) THU,
      5  sum(FRI) FRI,
      6  sum(SAT) SAT,
      7  sum(SUN) SUN
      8  from(
      9  select sch, decode(instr(sch,'1'), 0, '', 1) as "MON",
     10  decode(instr(sch,'2'), 0,'',1) as "TUE",
     11  decode(instr(sch,'3'), 0,'',1) as "WED",
     12  decode(instr(sch,'4'), 0,'',1) as "THU",
     13  decode(instr(sch,'5'), 0,'',1) as "FRI",
     14  decode(instr(sch,'6'), 0,'',1) as "SAT",
     15  decode(instr(sch,'7'), 0,'',1) as "SUN"
     16  from a
     17  );
    
           MON        TUE        WED        THU        FRI        SAT        SUN
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
             3          4          3          2          2          2          2
    Quote Originally Posted by ashazi
    Hi everyone,
    I'm a real newbie to this whole SQL/Oracle thing and although I've tried searching for the answers myself most of the time I find that I don't actually know what to look for. Unless I already know that a function exists to do what it is that I want to do, I am pretty much in the dark as to what to search for.

    I have a table that stores the details of all flights. The flight schedule is stored in a Schedule column in the following manner : '1234567' and 'Daily'. If the value is daily it means there is a flight every day. If the value is '1' then there is a flight on Monday, '2' means there is a flight on Tuesday, and so on.
    So there are various possible combinations, depending on the days which that flight is available.

    The problem is, I am trying to extract the day information from the table, as in trying to find out the number of flights that are available daily based on the values in the Schedule column and displaying the day as well as the number of flights on that day like this:

    Schedule : Total_Flights
    Monday 34
    Tuesday 45
    ...
    ..
    sunday 66

    Can anyone point me in the right direction?
    Thanks.

    ashazi
    Oracle can do wonders !

  3. #3
    Join Date
    May 2004
    Posts
    4
    Hi cmasharma,
    Thanks! It's beginning to amaze me how simple SQL can be manipulated to yield useful results.
    This does help a lot..

    ashazi

Posting Permissions

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