Skip to content
Advertisement

How to subtract Expenses from Gross Profit from sql plus query?

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.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement