將 NULL 計數為零並使用 WHERE 語句進行過濾

我有一個 SQL 謎題：

我們如何獲取購買了 3 隻或以下襪子的顧客（包括那些沒有購買任何襪子的顧客）的列表以及這些顧客購買了多少只襪子？

我遇到了一個問題，我最終得到了需要顯示為零的 NULL 值。但是，當我使用 CASE 語句選擇 NULL 作為零，然後使用 WHEN 語句過濾低於某些門檻值時，由於某種原因，零不會出現。

這是我的架構和一些虛擬值：

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);

這是我的查詢：

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;

但是，它不會產生所需的輸出：

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

我究竟做錯了什麼？產生預期結果的查詢是什麼？謝謝。

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

不需要帶有 HAVING 子句的子選擇：

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;

引用自：https://dba.stackexchange.com/questions/163307