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

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"

​x
 
(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",

 
(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",

 
(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

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

 
With Food_TotalAs(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_TOTALAs(   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

Advertisement

Answer