Mysql – COALESCE returning NULL when there are no records

MySQL

When running the following query on a device_id that has no entries in the inv_zfs_replication table the result is a NULL value. How can I make the 2nd sub-query return a zero if there aren't any records?

SELECT
    (
        (
            SELECT
                SUM(s.quota)
            FROM
                `inv_zfs_share` s
            JOIN `inv_zfs_project` p2 ON s.project_id = p2.id
            WHERE
                p2.device_id = p.device_id
            GROUP BY
                p2.pool_id
        ) + (
            SELECT
                COALESCE (SUM(quota), 0)
            FROM
                inv_zfs_replication
            WHERE
                device_id = p.device_id
            GROUP BY
                pool_id
        )
    ) AS 'Quota Total'
FROM
    inv_zfs_pool p
WHERE
    p.device_id = 626

Best Answer

Scalar subqueries that return no rows have a return "value" of NULL ... so all you need to do is coerce that NULL to a 0 directly outside the subquery with IFNULL():

SELECT
    (
        (
            SELECT
                SUM(s.quota)
            FROM
                `inv_zfs_share` s
            JOIN `inv_zfs_project` p2 ON s.project_id = p2.id
            WHERE
                p2.device_id = p.device_id
            GROUP BY
                p2.pool_id
        ) + 
         IFNULL( -- add this, here
           (
            SELECT
                SUM(quota)
            FROM
                inv_zfs_replication
            WHERE
                device_id = p.device_id
            GROUP BY
                pool_id
        )
      ,0) -- and add this, here
    ) AS 'Quota Total'
FROM
    inv_zfs_pool p
WHERE
    p.device_id = 626;
Related Question