Mysql – Multiplication Using Values From Various Tables

MySQL

I have created three table using with the following code:

CREATE TABLE Invoices (
    invoiceID nvarchar(5) NOT NULL PRIMARY KEY, 
    invoiceDate date, 
);

CREATE TABLE ContainsItems (
    invoiceID nvarchar(5) REFERENCES Invoices(invoiceID),
    itemID nvarchar(5) REFERENCES Items(itemID),
    itemQuantity numeric(2, 0)
);

CREATE TABLE Items (
    itemID nvarchar(5) NOT NULL PRIMARY KEY,
    itemName nvarchar(20),
    unit nvarchar(20), 
    pricePerUnit float
);

How would I be able to query a list of all invoices showing the invoiceID, invoiceDate, and the total cost of all items for each invoice?

Many Thanks.

Edit:

So far I have written something like:

SELECT Invoices.invoiceID, Invoices.invoiceDate, _______________________
FROM Invoices
INNER JOIN ContainsItems ON Invoices.invoiceID = ContainsItems.invoiceID 
INNER JOIN Items ON ContainsItems.itemID = Items.itemID;

…but I have no clue how to generate the total of each invoice.

I am using MySQL Workbench.

Best Answer

First, excellent that you provided create table statement. For additional bonus you can provide insert statements and expected output of the query ;-)

Second, some things are missing in your model. What is the primary key of ContainsItems, I guess it should be invoiceID,itemID and for that they need to be declared as NOT NULL. What does it mean if itemQuantity is null? I assume it can't be that (may be incorrect):

CREATE TABLE ContainsItems (
    invoiceID nvarchar(5) NOT NULL REFERENCES Invoices(invoiceID),
    itemID nvarchar(5) NOT NULL REFERENCES Items(itemID),
    itemQuantity numeric(2, 0),

    primary key (invoiceID, itemID)
);

In the same spirit I assume that an Item has a name, unit and PricePerUnit:

CREATE TABLE Items (
    itemID nvarchar(5) NOT NULL PRIMARY KEY,
    itemName nvarchar(20) NOT NULL,
    unit nvarchar(20) NOT NULL, 
    pricePerUnit float NOT NULL
);

If my assumptions are wrong you will have to adjust the query to handle this.

What you want is the SUM of the price of items per invoice. I assume this to be:

ContainsItems.itemQuantity * Items.pricePerUnit

Your query then only needs some slight modification. First we need to add information on what to apply the ? later on:

SELECT Invoices.invoiceID, Invoices.invoiceDate, ?
FROM Invoices
JOIN ContainsItems 
    ON Invoices.invoiceID = ContainsItems.invoiceID 
JOIN Items 
    ON ContainsItems.itemID = Items.itemID

GROUP BY Invoices.invoiceID, Invoices.invoiceDate

Now per such group we can sum the ContainsItems.itemQuantity * Items.pricePerUnit

SELECT Invoices.invoiceID, Invoices.invoiceDate

     , SUM( ContainsItems.itemQuantity * Items.pricePerUnit )

FROM Invoices
JOIN ContainsItems 
    ON Invoices.invoiceID = ContainsItems.invoiceID 
JOIN Items 
    ON ContainsItems.itemID = Items.itemID

GROUP BY Invoices.invoiceID, Invoices.invoiceDate
Related Question