I have to calculate Net profit So i have following query. How can i subtract “Total Expenses” from “Total Gross Profit”:
x
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.