Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
BTW, I've also tried replacing COALESCE with IFNULL without any luck. –  Chris Sep 25 '13 at 13:31
    
The two GROUP BY pool_id are either redundant or could cause error when you run the query and a subquery has more than 1 different pool_id. Inline subqueries must return either 1 or 0 values. –  ypercube Sep 25 '13 at 14:10
    
They aren't redundant, if you look they're on different tables. –  Chris Sep 25 '13 at 18:17
    
I meant they are both and individually redundant. For example in table inv_zfs_replication, is it possible that you have 2 rows with same device_id but different pool_id? –  ypercube Sep 25 '13 at 18:19
add comment

3 Answers

up vote 2 down vote accepted

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;
share|improve this answer
    
This was the simplest answer, thanks @michael-sqlbot. –  Chris Sep 25 '13 at 18:20
add comment

Put it outside. I think it will work

        SELECT COALESCE(
              (
              SELECT SUM(quota)
        FROM
            inv_zfs_replication
        WHERE
            device_id = p.device_id
        GROUP BY
            pool_id),0) AS 'Quota Total'
share|improve this answer
add comment

This does not work, because there is no row to return, so there is no column to COALESCE. If this is what you need, then you will need to force a row to be included. E.g.

        SELECT SUM(SumQuota) AS SumQuota -- Outer summation needed to 'coalesce' values 
        FROM (
        SELECT
           SUM(quota) AS SumQuota
        FROM
            inv_zfs_replication
        WHERE
            device_id = p.device_id
        GROUP BY
            pool_id
        UNION ALL  -- Forces a 0 value row to exist
        SELECT 0 AS SumQuota) AS ForceRow

Since you add 0 to the SUM, which might not exist, then you get either a 0 or the sum + 0, which is, of course no change.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.