4 Replies Latest reply: Mar 6, 2013 4:58 AM by user7375700 RSS

    Help in SQL or PL/SQL

    user7375700
      Hi,
      I have the following data:

      CREATE TABLE My_table
      (
      DEPART VARCHAR2(5 BYTE),
      ARRIVED VARCHAR2(9 BYTE),
      CODE_SEC VARCHAR2(2 BYTE),
      FIRST_SEC VARCHAR2(8 BYTE),
      LAST_SEC VARCHAR2(8 BYTE),
      ESTIMATE VARCHAR2(3 BYTE)
      );

      Insert into My_table (DEPART,ARRIVED,CODE_SEC,FIRST_SEC,LAST_SEC,ESTIMATE) values ('Paris','Marseille','A1','Paris','Lille','50');
      Insert into My_table (DEPART,ARRIVED,CODE_SEC,FIRST_SEC,LAST_SEC,ESTIMATE) values ('Paris','Marseille','A1','Nantes','Bordeaux','40');
      Insert into My_table (DEPART,ARRIVED,CODE_SEC,FIRST_SEC,LAST_SEC,ESTIMATE) values ('Paris','Marseille','A1','Bordeaux','Toulouse','25');
      Insert into My_table (DEPART,ARRIVED,CODE_SEC,FIRST_SEC,LAST_SEC,ESTIMATE) values ('Paris','Marseille','A2','Paris','Lille','50');
      Insert into My_table (DEPART,ARRIVED,CODE_SEC,FIRST_SEC,LAST_SEC,ESTIMATE) values ('Paris','Marseille','A2','Lille','Lyon','80');
      Insert into My_table (DEPART,ARRIVED,CODE_SEC,FIRST_SEC,LAST_SEC,ESTIMATE) values ('Paris','Marseille','A2','Lyon','Agen','100');
      Insert into My_table (DEPART,ARRIVED,CODE_SEC,FIRST_SEC,LAST_SEC,ESTIMATE) values ('Paris','Marseille','A2','Lyon','Paris','55');
      Insert into My_table (DEPART,ARRIVED,CODE_SEC,FIRST_SEC,LAST_SEC,ESTIMATE) values ('Paris','Marseille','A3','Paris','Lille','50');
      Insert into My_table (DEPART,ARRIVED,CODE_SEC,FIRST_SEC,LAST_SEC,ESTIMATE) values ('Paris','Marseille','A3','Troyes','Dax','30');
      Insert into My_table (DEPART,ARRIVED,CODE_SEC,FIRST_SEC,LAST_SEC,ESTIMATE) values ('Paris','Marseille','A3','Bordeaux','Toulouse','25');

      Depart     Arrived     code_Sec     First_Sec     Last_Sec     Estimate
      Paris     Marseille     A1     Paris     Lille     50
      Paris     Marseille     A1     Nantes     Bordeaux     40
      Paris     Marseille     A1     Bordeaux     Toulouse     25
      Paris     Marseille     A2     Paris     Lille     50
      Paris     Marseille     A2     Lille     Lyon     80
      Paris     Marseille     A2     Lyon     Agen     100
      Paris     Marseille     A2     Lyon     Paris     55
      Paris     Marseille     A3     Paris     Lille     50
      Paris     Marseille     A3     Troyes     Dax     30
      Paris     Marseille     A3     Bordeaux     Toulouse     25


      and I'd like to make the following calculation on these data:
      Add estimate for the Paris-Marseille, whatever the section, so that each pair (First_Sec, Last_Sec) is counted only once. for information, there may be other routes.
      in this example dssus: the total score must send:
      50 +40 +25 +80 +100 +55 +30 = 380

      Thank you in advance for your help.
        • 1. Re: Help in SQL or PL/SQL
          €$ħ₪
          May be this ...
          choose between ASC and DESC values on ESTIMATE
          
          SELECT SUM(ESTIMATE) FROM
          (
          select 
          ROW_NUMBER() OVER(PARTITION BY FIRST_SEC,LAST_SEC ORDER BY ESTIMATE) RN,
          DEPART,ARRIVED,CODE_SEC,ESTIMATE FROM My_table
          ) WHERE RN=1;
          • 2. Re: Help in SQL or PL/SQL
            user7375700
            Thank you for the answer but I would like the result of query (380) in this form :

            Depart - Arrived - code_Sec - First_Sec - Last_Sec - Estimate - Total
            Paris - Marseille - A1 - Paris - Lille - 50 - 380
            Paris - Marseille - A1 - Nantes - Bordeaux - 40 - 380
            PariS - Marseille - A1 - Bordeaux - Toulouse - 25 - 380
            Paris - Marseille - A2 - Paris - Lille - 50 - 380
            Paris - Marseille - A2 - Lille - Lyon - 80 - 380
            Paris - Marseille - A2 - Lyon - Agen - 100 - 380
            Paris - Marseille - A2 - Lyon - Paris - 55 - 380
            Paris - Marseille - A3 - Paris - Lille - 50 - 380
            Paris - Marseille - A3 - Troyes - Dax - 30 - 380
            Paris - Marseille - A3 - Bordeaux - Toulouse - 25 - 380
            • 3. Re: Help in SQL or PL/SQL
              €$ħ₪
              may be try this...
              WITH t AS
                   (SELECT ROW_NUMBER () OVER (PARTITION BY first_sec, last_sec ORDER BY ESTIMATE)
                                                                                         rn,
                           depart, arrived, code_sec, ESTIMATE, first_sec, last_sec
                      FROM my_table)
              SELECT depart, arrived, code_sec, MAX (sm_estimate) OVER (PARTITION BY NULL)
                FROM (SELECT depart, arrived, code_sec, ESTIMATE,
                             SUM (CASE
                                     WHEN rn = 1
                                        THEN TO_NUMBER (ESTIMATE)
                                     ELSE 0
                                  END) OVER (PARTITION BY rn) sm_estimate
                        FROM t)
              • 4. Re: Help in SQL or PL/SQL
                user7375700
                Thank you very much