Counting NULLs as zeroes and filtering with the WHERE statement

Question

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.

Answer 1

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            |
+-------+--------------+

Answer 2

There is no need for a sub-select with the HAVING clause:

SELECT c.Name, COALESCE(SUM(o.num_socks),0) num_socks
FROM Customers c
LEFT JOIN Orders o ON c.Cust_ID = o.Cust_Id
GROUP BY c.cust_ID, c.Name
HAVING COALESCE(sum(num_socks),0) < 3;

Answer 3

I figured this out. The problem was that the SAME case needs to be included in the WHERE statement because it is executed before the SELECT statement.

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 CASE WHEN socks_bought IS NULL THEN 0 ELSE socks_bought END < 3;