SQLTeam.com | Weblogs | Forums

SQL - Same table - Having c2.Country <> c.Country - need to understand

I tried the following SQL query and did not understand how last part in the parenthesis works. (SELECT COUNT(c2.CustomerID) FROM Customers AS c2 GROUP BY c2.Country Having c2.Country <> c.Country).

SELECT COUNT(c.CustomerID) as cnt, c.Country
FROM Customers c
GROUP BY c.Country
Having cnt NOT IN (SELECT COUNT(c2.CustomerID) FROM Customers AS c2 GROUP BY c2.Country Having c2.Country <> c.Country);

The first three lines give me country name and count of their customer id. For example:

Argentina 3

Belgium 3

Brazil 9

IRAN 2

Turkey 2

The last part (SELECT COUNT(c2.CustomerID) FROM Customers AS c2 GROUP BY c2.Country Having c2.Country <> c.Country)only gives me count customer id of country for which there is other countries with the same number of count. How it works? (For example:

3

2

and don't give the brazil.

I don't really understand the second condition of the HAVING query works: Having c2.Country <> c.Country. I know what c2.Country <> c.Country means. I don't understand how the bold line works.

When The query in the parenthesis is linked to the original query with a "HAVING NOT IN" which essentially compares the values from the first part of the query and the values from the second (Argentina 3, Belgium 3, IRAN 2, Turkey 2) and will return all rows that are not present in the second query, which is Brazil 9.

The HAVING ... NOT IN identifies any country that has the same total as another country and then prevents that country from being listed (such as for Argentina/Belgium). I have no idea why you'd want to do that, but that's what it does.

I mean how SELECT COUNT(c2.CustomerID) FROM Customers AS c2 GROUP BY c2.Country Having c2.Country <> c.Country) filter the result.