1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem to count rows

Discussion in 'SQL PL/SQL' started by tfs63, Dec 30, 2013.

  1. tfs63

    tfs63 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hello,

    I have rows that show a travel between two cities.

    I would like to count 1 trip when the place of arrival is the same as the place of departure for the row n+1.

    Please see the example below:

    LINE | DEPARTURE | ARRIVAL | TRIP COUNT NEEDED :

    1 ROUEN PARIS
    2 PARIS LYON 1
    -------------------------------------------------------------
    3 MARSEILLE PARIS 1
    -------------------------------------------------------------
    4 NANTES BORDEAUX 1


    Thanks in advance for your help
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This uses a simple join; what have you written to solve this problem?
     
  3. tfs63

    tfs63 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hello,

    Thanks for your reply

    I understand that a JOIN can be a solution, but i just want a JOIN between the row n and the row n+1.

    I didn't write the code because i dont know how to specify this particular point

    For the moment, i only have a general count :

    SELECT COUNT(*) FROM TRAVELS

    Thanks

    Tfs
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Apparently you don't understand joins so let me provide an example that should assist you:


    Code (SQL):

    SQL> CREATE TABLE travel_tab(
      2  line       NUMBER NOT NULL,
      3  departure       varchar2(30),
      4  arrival         varchar2(30),
      5  trip_comp               NUMBER);


    TABLE created.


    SQL>
    SQL> INSERT ALL
      2  INTO travel_tab
      3  VALUES(1, 'ROUEN', 'PARIS',  NULL)
      4  INTO travel_tab
      5  VALUES(2, 'PARIS', 'LYON', 1)
      6  INTO travel_tab
      7  VALUES(3, 'MARSEILLE', 'PARIS', 1)
      8  INTO travel_tab
      9  VALUES(4, 'NANTES','BORDEAUX',1)
     10  INTO travel_tab
     11  VALUES(5, 'BORDEAUX','LYON', NULL)
     12  INTO travel_tab
     13  VALUES(6, 'LYON', 'PARIS', 1)
     14  SELECT * FROM dual;


    6 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> SELECT d.departure, d.arrival waypoint, a.arrival, COUNT(*) trip_count
      2  FROM travel_tab d JOIN travel_tab a ON a.departure = d.arrival AND a.line = d.line + 1
      3  WHERE d.trip_comp IS NULL
      4  GROUP BY d.departure, d.arrival, a.arrival;


    DEPARTURE                      WAYPOINT                       ARRIVAL                        TRIP_COUNT
    ------------------------------ ------------------------------ ------------------------------ ----------
    ROUEN                          PARIS                          LYON                                    1
    BORDEAUX                       LYON                           PARIS                                   1


    SQL>

    Notice that the join IS between adjacent rows and that there is a 'controlling' condition -- a trip_comp column that lists if the trip has completed. When this is NULL there is still another leg to the journey; only process those rows where the trip is not considered complete.


    This may not be exactly as you have your table configured but there should be some 'indicator' of whether the trip is at its final destination or not.
     
  5. tfs63

    tfs63 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hello,

    i am actually a beginner in SQL
    Thank you for your help! I will try to adapt this code to my DB

    Regards,