I have to calculate Net profit So i have following query. How can i subtract “Total Expenses” from “Total Gross Profit”:
SELECT SUM(orders.quantity * orders.price) AS "Total Sale|", SUM(orders.quantity * supplied_items.cost_price) AS "Total COST|", SUM(orders.quantity *(orders.price - supplied_items.cost_price)) AS "Total Gross Profit|", max((Select SUM(expenses.amount) from expenses)) as "Total Expenses" FROM orders LEFT OUTER JOIN supplied_items ON orders.item_id = supplied_items.item_id;
Advertisement
Answer
Provided every Item_id
is sold and supplied only once
SELECT t1."Total Sale|", t1."Total COST|", t1."Total Gross Profit|" , t1."Total Gross Profit|" - t2."Total Expenses" AS profit FROM ( SELECT SUM(orders.quantity * orders.price) AS "Total Sale|", SUM(orders.quantity * supplied_items.cost_price) AS "Total COST|", SUM(orders.quantity * (orders.price - coalesce(supplied_items.cost_price, 0))) AS "Total Gross Profit|" FROM orders LEFT OUTER JOIN supplied_items ON orders.item_id = supplied_items.item_id; ) t1 CROSS JOIN ( SELECT SUM(expenses.amount) "Total Expenses" FROM expenses ) t2
Othrewise you need to aggregate orders
and supplied_items
by item_id
before joining.