I have this query: Which has this result: However, I'd like to add order_total and item_total in order to get just total. So I'd expect this: Doing this did not work: Another monkeywrench is that the type of the numbers is money not integer. Anyone would be able to help? Answer If any of the 2 sums returns NULL then

SQL: SUM on Aggregate columns

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 = 'AppOrder'
LEFT JOIN ordered_items
  ON spend_codeables.spend_codeable_id = ordered_items.id AND spend_codeables.spend_codeable_type = 'AppOrderedItem'
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?

Answer

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)

Advertisement

Answer