SQLite Query – SQL Query to Get COUNT() to Output NULL Instead of 0

sqlite

I have the following query, which basically counts the how many orders a customer has done:

SELECT name, count(order_id)
FROM customers
LEFT JOIN  orders
ON customers.name == orders.customername
GROUP BY name;

Output is something like this:

-------------
|adam |    2|
|bob  |    0|

Here bob had no entries in the orders table. So I believe the left join creates a table where adam has his order columns filled but bob does not.

But I want 0 to instead be NULL. Like this:

-------------
|adam |    2|
|bob  | NULL|

How can I do this?

Best Answer

One thing that works for a lot of engines, is using a CASE expression.

SELECT name,
       CASE
         WHEN count(order_id) = 0 THEN
           NULL
         ELSE
           count(order_id)
       END
       FROM customers
            LEFT JOIN orders
                      ON customers.name = orders.customername
       GROUP BY name;

And don't use == for comparison. In SQL it's just =.

Related Question