2

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.

2
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            |
+-------+--------------+
  • Thanks Dudu! Do you know why the CASE approach didn't work? – verkter Feb 6 '17 at 23:05
  • 1
    It didn't work because if socks_bought is null (left join) then it is not smaller from 3 (where socks_bought < 3) – David דודו Markovitz Feb 6 '17 at 23:09
  • OK, I think the problem was that I needed to include the same CASE in the WHERE statement, because it is executes before SELECT statement. – verkter Feb 6 '17 at 23:18
  • This will work (but it is not a matter of execution order) – David דודו Markovitz Feb 6 '17 at 23:20
  • Can you please elaborate? Thanks! – verkter Feb 6 '17 at 23:32
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;
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.