Counting NULLs as zeroes and filtering with the WHERE statement

join;nullwhere

I have a SQL puzzle:

How can we get a list of customers who have purchased 3 or fewer socks (including those who haven't purchased any socks) as well how many socks those customers have purchased?

I am running into a problem where I ended up with NULL values that need to be displayed as zeroes. However, when I use CASE statement to select NULLs as zeroes and then use WHEN statement to filter below certain threshold zeroes do not show up for some reason.

Here is my schema and some dummy values:

create table customers (
  cust_id TEXT,
  name TEXT
);

create table orders (
  order_id TEXT,
  cust_id TEXT,
  order_date DATE,
  num_socks INT
);

insert into customers values ('1001', 'Rudy');
insert into customers values ('1002', 'Jonny');
insert into customers values ('1003', 'Romeo');

insert into orders values ('1', '1001', '2015-05-01', 4);
insert into orders values ('2', '1001', '2015-04-03', 2);
insert into orders values ('3', '1003', '2015-04-10', 8);
insert into orders values ('4', '1003', '2016-05-10', 3);

Here is my query:

SELECT name, CASE WHEN socks_bought IS NULL THEN 0 ELSE socks_bought END AS socks_bought
FROM customers
LEFT JOIN
  (SELECT cust_id, sum(num_socks) AS socks_bought
   FROM orders
   GROUP BY cust_id) AS t1
    ON t1.cust_id = customers.cust_id
where socks_bought < 3;

However, it does not produce the desired output:

name | socks_bought
-------------------
Jonny | 0

What am I doing wrong? What is the query to produce the intended results? Thanks.

Best Answer

SELECT name, coalesce(socks_bought,0) AS socks_bought
FROM customers
LEFT JOIN
  (SELECT cust_id, sum(num_socks) AS socks_bought
   FROM orders
   GROUP BY cust_id) AS t1
    ON t1.cust_id = customers.cust_id
where coalesce(socks_bought,0) < 3;

+-------+--------------+
| name  | socks_bought |
+-------+--------------+
| Jonny | 0            |
+-------+--------------+
Related Question