CODEWITHSUNDEEP
mysqlcaseconditional-statementsifnull
coffeemonitor
coffeemonitor

Reputation: 13130

MySQL - Complicated CASE/WHEN Condition possible?

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

Upvotes: 0

Views: 248

Answers (2)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

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

Upvotes: 2

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

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

Upvotes: 1

Related Questions