SQL: SUM on Aggregate columns-CodePudding

I have this query:

SELECT
  spend_codes.spend_code,
  SUM(orders.shipping_cost   orders.sales_tax   orders.manual_total   orders.fees) as order_total,
  SUM(ordered_items.fees   ordered_items.price * ordered_items.quantity) as item_total
FROM spend_codeables
LEFT JOIN spend_codes
  ON spend_codeables.spend_code_id = spend_codes.id
LEFT JOIN orders
  ON spend_codeables.spend_codeable_id = orders.id AND spend_codeables.spend_codeable_type = 'App\Order'
LEFT JOIN ordered_items
  ON spend_codeables.spend_codeable_id = ordered_items.id AND spend_codeables.spend_codeable_type = 'App\OrderedItem'
GROUP BY
  spend_codes.spend_code;

Which has this result:

 -------------- --------------- -------------- 
| spend_code   | order_total   | item_total   |
|-------------- --------------- --------------|
| 1230131391   | $362.00       | <null>       |
| A12345       | <null>        | <null>       |
| B29393       | <null>        | $374.28      |
 -------------- --------------- --------------

However, I'd like to add order_total and item_total in order to get just total.

So I'd expect this:

 -------------- --------------- 
| spend_code   | total         |
|-------------- --------------- 
| 1230131391   | $362.00       |
| A12345       | <null>        |
| B29393       | $374.28       |
 -------------- ---------------

Doing this did not work:

  SUM(orders.shipping_cost   orders.sales_tax   orders.manual_total   orders.fees)  
  SUM(ordered_items.fees   ordered_items.price * ordered_items.quantity) as total

Another monkeywrench is that the type of the numbers is money not integer.

Anyone would be able to help?

CodePudding user response：

If any of the 2 sums returns NULL then the result of the sum of the sums will also be NULL because NULL anything returns NULL.

Use SUM() only once:

SUM(
  orders.shipping_cost   
  orders.sales_tax   
  orders.manual_total   
  orders.fees  
  ordered_items.fees   
  ordered_items.price * ordered_items.quantity
) as total

If any of the columns involved may also be NULL use COALESCE() like COALESCE(orders.shipping_cost, 0::money)

CodePudding user response：

you do it in next step using sub query and I used coalesce() for avoid null

   with cte as ( SELECT
      spend_codes.spend_code,
      SUM(orders.shipping_cost   orders.sales_tax   orders.manual_total   orders.fees) as order_total,
      SUM(ordered_items.fees   ordered_items.price * ordered_items.quantity) as item_total
    FROM spend_codeables
    LEFT JOIN spend_codes
      ON spend_codeables.spend_code_id = spend_codes.id
    LEFT JOIN orders
      ON spend_codeables.spend_codeable_id = orders.id AND spend_codeables.spend_codeable_type = 'App\Order'
    LEFT JOIN ordered_items
      ON spend_codeables.spend_codeable_id = ordered_items.id AND spend_codeables.spend_codeable_type = 'App\OrderedItem'
    GROUP BY
      spend_codes.spend_code
) select spend_code,coalesce(order_total,0) coalesce(item_total,0) as total from cte