SUMs, Joins and COALESCE in MySQL

Recently I ran into a problem with a query. I was trying to add the sums of two columns together, one of the columns happened to be in a table that was LEFT OUTER JOINed and the result was NULL when it should not have been.

This is for a purchase ordering system and is the calculation to get the total of inventory that is in transit, taking into account partial receipts of inventory.

Here is my table structure
sums-and-joins-tables

select SUM( po_item_qty ) - SUM( po_receipt_qty ) as on_order

from po_item

left outer join po_receipt on ( po_receipt_po_item_id = po_item_id )
left outer join po on ( po_item_po_id = po_id )
left outer join product on ( po_item_product_id = product_id )

where po_status = 'in_transit'

This produces a result of ‘NULL’ when I know perfectly good and well that I have in transit purchase orders with po_item records.

It turns out that subtracting the po_receipt_qty when there are no matching po_receipt records (LEFT OUTER JOIN records return null when missing ) turns into, for example, 100 – NULL which always returns NULL.

What I need is for this to turn into 100 – 0, returning 100.

COALESCE to the rescue!

The query that made it work:

select SUM( po_item_qty ) - COALESCE( SUM( po_receipt_qty ) ,0 ) as on_order

from po_item

left outer join po_receipt on ( po_receipt_po_item_id = po_item_id )
left outer join po on ( po_item_po_id = po_id )
left outer join product on ( po_item_product_id = product_id )

where po_status = 'in_transit'
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s