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?
Advertisement
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)