CODEWITHSUNDEEP
mysqlsql
Yellowjacket11
Yellowjacket11

Reputation: 71

Calculating total price for each order in a restaurant database by multiplying the price and quantity columns located in two separate tables

I have two tables: Contain and FoodItem. Their description and contents are shown below.

CREATE TABLE Customer (
CustomerID INT NOT NULL AUTO_INCREMENT,
Fname VARCHAR(10),
Lname VARCHAR(10), 
PRIMARY KEY (CustomerID)
);

CREATE TABLE Orders (
OrderNo INT NOT NULL AUTO_INCREMENT,
OrderDate DATETIME NOT NULL, 
CustomerID INT,
PRIMARY KEY (OrderNo),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

CREATE TABLE FoodInfo (
ItemName VARCHAR(10) NOT NULL,
Description VARCHAR(10),
PRIMARY KEY (ItemName)
);

CREATE TABLE FoodItem (
ItemName VARCHAR(10),
OrderNo INT,
Price SMALLINT,
PRIMARY KEY (ItemName, OrderNo),
FOREIGN KEY(OrderNo) REFERENCES Orders (OrderNo),
FOREIGN KEY(ItemName) REFERENCES FoodInfo (ItemName)
);

CREATE TABLE Contain(
    OrderNo INT,
    ItemName VARCHAR(10),
    Quantity INT,
    PRIMARY KEY(OrderNo, ItemName),
    FOREIGN KEY(OrderNo) REFERENCES Orders (OrderNo),
    FOREIGN KEY(ItemName) REFERENCES FoodInfo (ItemName)
    );

INSERT INTO Customer VALUES
(1, Velda, Quigley),
(2, Abelardo, Stoltenber),
(3, Ludwig, Dare),
(4, Jordan, Beahan),
(5, Saul, Rohan),
(6, Una, Bergnaum),
(7, Richie, Bauch),
(8, Rubie, Gibson),
(9, Holden, Nader),
(10, Lance, Jacobi);



INSERT INTO Orders VALUES 
(2019-10-09 01:22:26, 1),
(1987-07-06 05:54:57, 2),
(1982-12-01 02:03:54, 3),
(2008-01-18 22:15:48, 9),
(2006-01-13 23:24:00, 2),
(1983-02-02 19:57:23, 5),
(1976-08-12 20:54:17, 4),
(1988-10-27 00:07:06, 8),
(2006-11-17 18:11:30, 5),
(2006-10-20 02:52:03, 10);

INSERT INTO FoodInfo VALUES
(assumenda, Architecto),
(iste, Totam est ),
(laborum, Maxime et ),
(neque, Est qui eu),
(quidem, Reiciendis),
(quos, Velit eum ),
(recusandae, Ea est fug),
(repudianda, Cumque aut),
(sit, Rem est te),
(sunt, Reiciendis);

INSERT INTO FoodItem VALUES
(assumenda, 1, 5),
(iste, 2, 10),
(laborum, 3, 20),
(neque, 1, 23),
(quidem, 2, 44),
(quos, 3, 51),
(recusandae, 7, 56),
(repudianda, 8, 71),
(sit, 9, 98),
(sunt, 10, 23);

INSERT INTO Contain VALUES 
 (1, 'assumenda', 1),
 (2, 'iste', 2),
 (3, 'laborum', 3),
 (1, 'neque', 1),
 (2, 'quidem', 2),
 (3, 'quos', 4),
 (7, 'recusandae', 5),
 (8, 'repudianda', 2),
 (9, 'sit', 1),
 (10, 'sunt', 1);

My goal is to multiply the value of each item's quantity and price that are associated with an order number and sum them to get the total amount incurred. Here is what I have tried:

SELECT Contain.OrderNo
     , Contain.Quantity
     , FoodItem.Price 
  FROM Contain
     , FoodItem 
 WHERE Contain.OrderNo = FoodItem.OrderNo;

+---------+----------+-------+
| OrderNo | Quantity | Price |
+---------+----------+-------+
|       1 |        1 |     5 |
|       1 |        1 |    23 |
|       1 |        1 |     5 |
|       1 |        1 |    23 |
|       2 |        2 |    10 |
|       2 |        2 |    44 |
|       2 |        2 |    10 |
|       2 |        2 |    44 |
|       3 |        3 |    20 |
|       3 |        3 |    51 |
|       3 |        4 |    20 |
|       3 |        4 |    51 |
|       7 |        5 |    56 |
|       8 |        2 |    71 |
|       9 |        1 |    98 |
|      10 |        1 |    23 |
+---------+----------+-------+
16 rows in set (0.00 sec)

Using GROUP BY:

SELECT FoodItem.OrderNo
     , SUM(FoodItem.Price * Contain.Quantity) AS TOTAL 
  FROM FoodItem 
  JOIN Contain 
    ON FoodItem.OrderNo = Contain.OrderNo 
 GROUP 
    BY Contain.OrderNo;

+---------+-------+
| OrderNo | TOTAL |
+---------+-------+
|       1 |    56 |
|       2 |   216 |
|       3 |   264 |
|       7 |   280 |
|       8 |   142 |
|       9 |    98 |
|      10 |    23 |
+---------+-------+
7 rows in set (0.00 sec)

However, my desired output looks like this:

DESIRED OUTPUT:

+---------+-------+
| OrderNo | TOTAL |
+---------+-------+
|       1 |    28 |
|       2 |   108 |
|       3 |   264 |
|       7 |   280 |
|       8 |   142 |
|       9 |    98 |
|      10 |    23 |
+---------+-------+

I cannot figure out the correct SQL statement to generate this result. Could anyone please help me out?

Upvotes: 3

Views: 3541

Answers (2)

nbk
nbk

Reputation: 49375

Add another column ItemName to your join to make it unique, else you get a cross join

SELECT 
    FoodItem.OrderNo,
    SUM(FoodItem.Price * Contain.Quantity) AS TOTAL
FROM
    FoodItem
        JOIN
    Contain ON FoodItem.OrderNo = Contain.OrderNo AND FoodItem.`ItemName` = Contain.`ItemName`
GROUP BY Contain.OrderNo;

OrderNo | TOTAL
------: | ----:
      1 |    28
      2 |   108
      3 |   264
      7 |   280
      8 |   142
      9 |    98
     10 |    23

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You need to include ItemName in the JOIN as well:

SELECT fi.OrderNo, SUM(dfi.Price * c.Quantity) AS TOTAL 
FROM FoodItem fi JOIN
     Contain c
     ON fi.OrderNo = c.OrderNo AND
        fi.ItemName = c.ItemName
GROUP BY c.OrderNo;

The match is not just on one column, but on two. You need both or rows that should not match do match -- and the results are multiplied.

Upvotes: 2

Related Questions