finding value in a list created via subquery

Question

Thank you Stack-Community,

This is probably obvious for most of you but I just don't understand why it doesn't work.

I am using the Northwind database and lets say I am trying to find the countries that or not occurring twice but are listed either more than twice or less often.

I already figured out other ways of doing it with a having statement, so I am not looking for alternatives but trying to understand why my initial attempt is not working.

I look at it and look at it and it makes perfect sense to me. Can someone explain what's the problem?

SELECT  country, count(country)
FROM Customers
WHERE 2 not in (SELECT count(country) FROM Customers GROUP BY country) 
GROUP BY country
;

Answer 1

You need correlated subquery:

SELECT  country, count(country)
FROM Customers c
WHERE 2 not in (SELECT count(country) FROM Customers c2 
                WHERE c2.country = c.country ) 
GROUP BY country;

Otherwise you get something like:

SELECT  country, count(country)
FROM Customers c
WHERE 2 not in (1,2,3) -- false in every case and empty resultset
GROUP BY country;

---

Imagine that you have:

1, 'UK'    -- 1
2, 'DE'    -- 2
3, 'DE'
4, 'RU'    -- 1

Now you will get equivalent of

SELECT  country, count(country)
FROM Customers c
WHERE 2 not in (1,2,1) -- false in every case and empty resultset
GROUP BY country;

 -- 0 rows selected