title: Correct SQL Multiply across tables
body: <p>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.</p>
  
  <p>Keep in mind that I'm new to this. I am trying to get the code to make subtotal equal the sum of column <strong>qty</strong> multiplied by the sum of column <strong>donutPrice</strong> 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.</p>
  
  <p>The ultimate goal is to make it kinda automated.</p>
  
  <pre><code>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;
  </code></pre>