1

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.

2
  • This is a basic join and grop by statement. What have you tried? Also: which DBMS are you using? "SQL" is only a query language, not a DBMS product (although the DBMS is most probably irrelevant for such a basic question)
    – user1822
    Commented Sep 13, 2016 at 6:13
  • hey @a_horse_with_no_name , I have added what I've written so far. Any help would be greatly appreciated! Thanks. Commented Sep 13, 2016 at 8:10

1 Answer 1

1

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
1
  • Not only is this answer 100% correct, but it has taught me so much. Thank you so much buddy! :) Commented Sep 14, 2016 at 0:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.