Working sales and profit out

01 / 04

The sales figure is nowhere in the tables

orders holds only quantities and items only unit prices. There is no money column in any table.

So you join them and make it with a multiplication. quantity * price is what that one order came to.

02 / 04

Tie orders and items together on the number, then multiply. Give the calculated column a name with AS.

To make clear which table a column is from, put a short alias like o. or i. in front.

SELECT o.id,       o.quantity * i.price AS sales  FROM orders o  JOIN items i    ON o.item_id = i.id  ORDER BY o.id LIMIT 3;

Result

1 | 1600
2 | 1500
3 | 2000

03 / 04

Gather the per-order amounts with SUM and you have the total takings. Add a GROUP BY and you have takings per item or per category.

The trick is putting the multiplication straight inside the brackets of the SUM.

SELECT SUM(o.quantity * i.price)         AS sales  FROM orders o  JOIN items i    ON o.item_id = i.id;

Result

04 / 04

Profit is "the selling price less the cost" multiplied by the quantity. Mind where the brackets go.

An item with big sales and small profit is a common thing. Get into the habit of looking at both together. Let us write some.

SELECT SUM(o.quantity *           (i.price - i.cost))         AS profit  FROM orders o  JOIN items i    ON o.item_id = i.id;

Result