MySQL - Complicated CASE/WHEN Condition possible?

Question

This question is related to my previous post: MySQL - Complicated SUMs inside Query

The query is working great, summing up the totals as desired. I notice that it also ignores any records with NULL values.

Current query:

SELECT c.*,
        SUM(CASE WHEN billtype = 1 THEN total ELSE 0 END) totalpaid ,
        SUM(CASE WHEN billtype = 2 THEN total ELSE 0 END) totalowed ,
        SUM(total) AS totalbalance
FROM 
    tbl_customers c
    LEFT JOIN tbl_customers_bills  b
    ON c.customerid = = b.customerid
     and billtype in (1,2)
GROUP BY 
     c.customerid

And it returns 10 customer records beautifully. As I check the database, I can see 11 customer records, and the eleventh has no correlating records inside the tbl_customers_bills table.

I'd like to still return all eleven, even if there are non-existing records in the tbl_customers_bills table. (but with zeros, of course)

I'm stuck with how complicated such a condition can get. Here's what I've tried: (to no avail)

SELECT c.*,

     (CASE WHEN (total IS NULL) THEN totalpaid = 0

       ELSE
        SUM(CASE WHEN billtype = 1 THEN total ELSE 0 END) totalpaid ,
        SUM(CASE WHEN billtype = 2 THEN total ELSE 0 END) totalowed ,
        SUM(total) AS totalbalance
      END)

FROM 
    tbl_customers c
    LEFT JOIN tbl_customers_bills  b
    ON c.customerid = = b.customerid
     and billtype in (1,2)
GROUP BY 
     c.customerid

@ypercube - I would assume it belongs to `tbl_customers_bills` but I don't understand why you think a `where` clause is missing. — Lieven Keersmaekers, Jan 17 '13 at 07:57
Yes it does but I believe OP just want to replace the NULL's for the 11th row with 0's. But as I mentioned in my answer, "I might very well be totally of" — Lieven Keersmaekers, Jan 17 '13 at 08:01

score 2 · Accepted Answer · answered Jan 17 '13 at 07:30

I might very well be totally of but I believe you are just missing COALESCE

SELECT c.*,
       COALESCE(SUM(CASE WHEN billtype = 1 THEN total ELSE 0 END), 0) totalpaid ,
       COALESCE(SUM(CASE WHEN billtype = 2 THEN total ELSE 0 END), 0 totalowed ,
       COALESCE(SUM(total), 0) AS totalbalance
FROM 
    tbl_customers c
    LEFT JOIN tbl_customers_bills  b
    ON c.customerid = = b.customerid
     and billtype in (1,2)
GROUP BY 
     c.customerid

From the MySQL Reference

COALESCE(value,...)

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

mysql> SELECT COALESCE(NULL,1); -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL

score 1 · Answer 2 · answered Jan 17 '13 at 07:25

How about doing ifnull

SELECT 
    c.*,
    SUM(CASE WHEN billtype = 1 THEN IFNULL(total,0) ELSE 0 END) totalpaid ,
    SUM(CASE WHEN billtype = 2 THEN IFNULL(total,0) ELSE 0 END) totalowed ,
    SUM(total) AS totalbalance
FROM 
    tbl_customers c
    LEFT JOIN tbl_customers_bills  b
    ON c.customerid = = b.customerid
     and billtype in (1,2)
GROUP BY 
     c.customerid

MySQL - Complicated CASE/WHEN Condition possible?

2 Answers2