CODEWITHSUNDEEP
sql
walter
walter

Reputation: 47

I'm trying to find the total of my invoice sql

So I have two tables invoice_w and items_sold_w. I'm trying to multiply the items sold * the price of each item and then add the tax, shipping, discount and installation fee. The code runs but the problem is that it calculates the tax, discount, shipping, and installation by how many different items are on the invoice so if the invoice has 2 items it will charge them double shipping, tax, discount, and installation.

SELECT DISTINCT invoice_no, invoice_no(SUM((item_qty_sold*item_price)+invoice_tax+invoice_shipping-invoice_discount+invoice_installation_fee)) "Total Price"
FROM invoices_w JOIN
     items_sold_w
     USING (invoice_no)
GROUP BY invoice_no;

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Is this what you want?

SELECT invoice_no,
       (SUM(item_qty_sold * item_price) + invoice_tax + invoice_shipping - invoice_discount + invoice_installation_fee) as Total_Price
FROM invoices_w JOIN
     items_sold_w
     USING (invoice_no)
GROUP BY invoice_no, invoice_tax, invoice_shipping, invoice_discount, invoice_installation_fee;

If any of the invoice values are NULL, you'll need to use COALESCE().

Upvotes: 1

Related Questions