CODEWITHSUNDEEP
mysqlsqlfiddle
Rob Probasco
Rob Probasco

Reputation: 1

Correct SQL Multiply across tables

I am at the end of my rope. I am learning SQL for a class. I have tried to get something akin to the to work, but to no avail. Can someone take a look at it.

Keep in mind that I'm new to this. I am trying to get the code to make subtotal equal the sum of column qty multiplied by the sum of column donutPrice in the donut table. I can't find much except for joins and if I do that, I can't use the join as a value.

The ultimate goal is to make it kinda automated.

CREATE TABLE donut
(
  donutID int(50) not null auto_increment primary key,
  donutName varchar(50) not null,
  donutDesc varchar(200),
  donutPrice dec(8,2)
);
CREATE TABLE customer
(
  customerID int(50) not null auto_increment primary key,
  fname char(50) not null,
  lname char(50) not null,
  address varchar(50) not null,
  apartment varchar(10),
  city char(50) not null,
  state char(2) not null,
  zip dec(5) not null,
  homeph varchar(10),
  mobileph varchar(10),
  otherph varchar(10)
);
CREATE TABLE invoice
(
  orderID int(50) not null auto_increment primary key,
  notes varchar(50) not null,
  orderdate date not null,
  customerID int(50) not null default 1,
  foreign key (customerID) references customer(customerID)
);
CREATE TABLE invoice_line_item
(
  donutID int(50) not null,
  orderID int(50) not null,
  qty dec not null,
  subtotal dec(10,2),
  subtotal= sum('qty'*'donutPrice') FROM (invoice_line_item, donut),
  primary key (donutID, orderID),
  foreign key(donutID) references donut(donutID),
  foreign key(orderID) references invoice(orderID)
);

ALTER TABLE donut AUTO_INCREMENT=1;
ALTER TABLE customer AUTO_INCREMENT=1001;
ALTER TABLE invoice AUTO_INCREMENT=500;

Upvotes: 0

Views: 40

Answers (1)

O. Jones
O. Jones

Reputation: 108796

I guess you want a result looking like this:

OrderID  subtotal
  1       12.50
  2       15.00
          27.50

You get that with a query like this:

 SELECT invoice.orderID, SUM(invoice_line_item.qty * donut.donutPrice) subtotal
   FROM invoice
   JOIN invoice_line_item ON invoice.orderID = invoice_line_item.orderID
   JOIN donut ON invoice_line_item.donutID = donut.donutID
  GROUP BY invoice.orderID WITH ROLLUP

Did you cover entity-relationship data in your class? Your entities are invoice, invoice_line_item, and donut (and your other tables). The relationships between them appear in the ON clauses of the JOIN operations.

Start with a query, and get it working. Then you can create a view ... which is nothing more or less than an encapsulated query.

Upvotes: 1

Related Questions