Problem with SQL function

Question

I have a function

CREATE OR REPLACE FUNCTION Total_order_price 
     (p_order#  IN NUMBER) 
RETURN NUMBER 
IS 
  l_total_order_amount  NUMBER := 0; 
BEGIN 
  SELECT Sum(fo.n_portions * di.price) + Sum(do.n_units * dr.price) AS total_order_price 
  INTO   l_total_order_amount 
  FROM   customer_order co, 
         food_order fo, 
         drink_order do, 
         dish di, 
         drink dr 
  WHERE  co.order# = fo.order# 
         AND fo.dish# = di.dish# 
         AND co.order# = do.order# 
         AND do.drink# = dr.drink# 
         AND co.order# = p_order#; 

  RETURN l_total_order_amount; 
END; 
/
select total_order_price(1) from dual;

It is meant add up the total of an order when I run it in sql developer using order 1 I get this:

TOTAL_ORDER_PRICE(1)   
---------------------- 
622.5

but it should be 207.50.

Here is the table data and table creation:

create table Customer_order( order# NUMBER primary key , 
date_order    DATE,
  date_required DATE,
  address       VARCHAR2(30)
  );
CREATE  TABLE Dish (
  dish#      VARCHAR2(5) primary key,
  dish_name  CHAR(15),
  vegetarian CHAR(3),
  price      NUMBER);

 CREATE  TABLE Drink (
  drink#     VARCHAR2(6) primary key,
  drink_name CHAR(6),
  drink_type CHAR(9),
  price      NUMBER);

CREATE  TABLE Food_order (
  food_order# VARCHAR2(7) primary key,
  order#      number references Customer_order (order#) ,
  dish#       VARCHAR2(5) references Dish(Dish#),
  n_portions  NUMBER);

CREATE  TABLE Drink_order (
  drink_order# VARCHAR2(5)primary key,
  order#       number references Customer_order (order#),
  drink#       VARCHAR2(6)references drink (drink#),
  n_units      NUMBER);


-- data insert
Insert into customer_order values ('00001', '03-Apr-09', '07-apr-09','St. Andrew St'); 
Insert into customer_order values ('00002', '05-Apr-09', '01-May-09', 'St. Andrew St');
Insert into customer_order values ('00003', '12-Apr-09', '27-Apr-09', 'Union St');
Insert into customer_order values ('00004', '12-Apr-09', '17-Apr-09', 'St. Andrew St');

Insert into Dish values ('D0001', 'Pasta bake',      'yes', '6.00'); 
Insert into Dish values ('D0002', 'Fish pie',        'no',  '9.00');  
Insert into Dish values ('D0003', 'Steak and chips', 'no',  '14.00');   
Insert into Dish values ('D0004', 'Stuffed peppers', 'yes', '11.50');   
Insert into Dish values ('D0005', 'Ham and rice'   , 'no',  '7.25');  
Insert into Dish values ('D0006', 'Lamb curry'     , 'no',  '8.50'); 

Insert into Drink values ('DR0001', 'Water',  'soft',      '1.0');
Insert into Drink values ('DR0002', 'Coffee', 'hot',       '1.70');
Insert into Drink values ('DR0003', 'Wine'  , 'alcoholic', '3.00'); 
Insert into Drink values ('DR0004', 'Beer'  , 'alcoholic', '2.30');  
Insert into Drink values ('DR0005', 'Tea'   , 'hot'     ,  '1.50');   

Insert into food_order values ('F000001', '000001', 'D0003', '6');
Insert into food_order values ('F000002', '000001', 'D0001', '4');
Insert into food_order values ('F000003', '000001', 'D0004', '3');
Insert into food_order values ('F000004', '000002', 'D0001', '10');
Insert into food_order values ('F000005', '000002', 'D0002', '10');
Insert into food_order values ('F000006', '000003', 'D0002', '35');
Insert into food_order values ('F000007', '000004', 'D0002', '23');

Insert into drink_order values ('D000001', '000001', 'DR0001', '13');
Insert into drink_order values ('D000002', '000001', 'DR0002', '13');
Insert into drink_order values ('D000003', '000001', 'DR0004', '13');
Insert into drink_order values ('D000004', '000002', 'DROOO1', '20');
Insert into drink_order values ('D000005', '000002', 'DR0003', '20');
Insert into drink_order values ('D000006', '000002', 'DR0004', '15');
Insert into drink_order values ('D000007', '000003', 'DR0002', '35');
Insert into drink_order values ('D000008', '000004', 'DR0001', '23'); 
Insert into drink_order values ('D000009', '000004', 'DR0003', '15');
Insert into drink_order values ('D0000010', '000004', 'DR0004', '15');

So what should I do to get 207.50 instead of 622.5?

Answer 1

If you have multiples of drinks orders and multiples of food orders, the joins will cross multiply, giving you far more than you wanted.

This will give you the two parts separately which should add up to your required total:

SELECT Sum(fo.n_portions * di.price) AS food_order_price   
FROM   customer_order co,
          food_order fo,
          dish di,
WHERE  co.order# = fo.order#
          AND fo.dish# = di.dish#
          AND co.order# = p_order#; 

SELECT Sum(do.n_units * dr.price) AS drinks_order_price   
FROM   customer_order co,
          drink_order do,
          drink dr   
WHERE  co.order# = do.order#
          AND do.drink# = dr.drink#
          AND co.order# = p_order#;

Answer 2

I didn't check the data in detail, but you probably have something like two drink orders, and two food orders, resulting in 4 rows in the select. When you add everything up you count the orders twice.

To fix this, do two separate selects, on for the food orders, one for the drink orders. And add the results in the procedure or using a union.

Answer 3

You don't have a 1 to 1 relationship with the records in food orders and drink orders. Which is why you would be better off with a table to hold all the ordered items. Your Dish and Drink tables seem to be the same as well. I would put them into one table and have a 'type' field that has the values of either food or drink.

In this database, are you going to create another table if this place sells toys or tshirts?