Sum values from two tables ORACLE

Question

I want to use two values, in two different tables, to be used as a total in a third table.

For instance:

1. table accommodation and flight
2. Add value from acc_price with value from flight_price
3. Add those values up to be used in table reservation subtotal

Things I've tried;

SELECT  F.FLI_PRICE + AC.ACC_PRICEPN
INTO    R.SUBTOTAL
FROM    HOLIDAY_RESERVATION R, FLIGHT F, ACCOMMODATION AC;

UPDATE

I wish to add the code from Leigh into a trigger, i tried the following but got a mutation error

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
AFTER DELETE OR INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL = 
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) +
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0) +
   NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0);
END;
/

ERROR at line 1:
ORA-04091: table LATIN.HOLIDAY_RESERVATION is mutating, trigger/f
ORA-06512: at "LATIN.HR_SUBTOTAL", line 2
ORA-04088: error during execution of trigger 'LATIN.HR_SUBTOTAL'

Answer 1

To update a column using a sum of values from other tables that may have nulls, do something like this:

UPDATE Holiday_Reservation R SET Subtotal = 
   NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.In_Flight_ID), 0) +
   NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.Out_Flight_ID), 0) +
   NVL((SELECT AC.Acc_PricePn FROM Accommodation AC WHERE AC.Acc_ID = R.Acc_ID), 0);

In a trigger this would look something like this:

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
BEFORE INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
   SELECT 
      NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = :new.In_Flight_ID), 0) +
      NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = :new.Out_Flight_ID), 0) +
      NVL((SELECT AC.Acc_PricePn FROM Accomodation AC WHERE AC.Acc_ID = :new.Acc_ID), 0)
      INTO :new.Subtotal
   FROM dual;
END;
/

Answer 2

You didn't explain what is the join relationship between the tables...

I propose the following:

select F.key_col, F_price + AC_price as total_price
FROM
    (SELECT key_col, sum(F.FLI_PRICE) as F_price
    from FLIGHT F
    group by key_col) as F
  inner join 
    (SELECT key_col, sum(AC.ACC_PRICEPN) as AC_price
    from ACCOMMODATION AC
    group by key_col) as AC 
  on F.key_col = AC.key_col

Add any other tables as you wish. If you want to filter and have better performance, please provide more information.