This discussion is archived
4 Replies Latest reply: Mar 6, 2013 2:58 AM by 988963 RSS

Help in SQL or PL/SQL

988963 Newbie
Currently Being Moderated
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
    €$ħ₪ Expert
    Currently Being Moderated
    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
    988963 Newbie
    Currently Being Moderated
    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
    €$ħ₪ Expert
    Currently Being Moderated
    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
    988963 Newbie
    Currently Being Moderated
    Thank you very much

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points