SQL - sum a column with common id, then coalesce total with another column

Question

I know somebody can tell me what I'm doing incorrectly here. This is a postgresql db. I'm trying to first sum the values in a column that share the same ID, and then add that total (coalesce) with another column.

First, this is how I total up the values within a column that share a common order.id:

(
SELECT
    SUM(order_item.purchase_price)
    from order_item
    where order_item.order_id = orders.id
    group by orders.id, order_item.id
) as "Food Total"

This is how I add the values of 2 columns together:

(
SELECT
    COALESCE(order_item.purchase_price,0) + COALESCE(order_item.iva_on_purchase_price,0)
    from order_item
    where order_item.order_id = orders.id
) as "Order Total",

So how might I go about combining the two, and first finding the sum of all values that share a common order.id, and then adding that sum to the other column (order_item.iva_on_purchase_price).

Would this be incorrect?

(
SELECT
    SUM(order_item.purchase_price) - COALESCE(order_item.iva_on_purchase_price,0)
    from order_item
    where order_item.order_id = orders.id
    group by orders.id, order_item.id
) as "Buyer Food Total Pre IVA",

What's the right way to do something like this?

Answer 1

You can create two CTE one for first sum and other for second data you want and then you can join the two CTE see below example code

With Food_Total
As(
SELECT
    SUM(order_item.purchase_price) As SUM_PP, orders.id As ORDER_ID,  order_item.id As ORDER_ITEM_ID
    from order_item
    where order_item.order_id = orders.id
    group by orders.id, order_item.id
),
ORDER_TOTAL
As
(
   SELECT
    COALESCE(order_item.iva_on_purchase_price,0) As PP, orders.id As ORDER_ID, order_id As ORDER_ITEM_ID
    from order_item
    where order_item.order_id = orders.id
)
Select (SUM_PP + PP) TOTAL_SUM,
        From Food_Total ft
        JOIN ORDER_TOTAL ot On ft.ORDER_ID = ot.ORDER_ID And ft.ORDER_ITEM_ID = ot.ORDER_ITEM_ID

if you generate sql fiddle with sample table we can try result