sqlsql-match-all

Vanilla SQL that selects multiple values in single column

If I have a table with customer IDs in one column and time zones in another, is there a plain SQL statement that can select all customer IDs that have different time zone values? In other words, I want to find those customers with offices in New York, Chicago, and San Francisco but not those who ONLY have an office in one or the other time zones.

Solution 
  • SELECT Customer
FROM MyTable
GROUP BY Customer
HAVING COUNT(DISTINCT TimeZone) > 1

    The use of DISTINCT is important.

    COUNT(TimeZone) counts all non-null values, not just distinct values. So it's equivalent to COUNT(*) except where TimeZone is null.

    In other words, if a given customer has three offices, but all are in the Eastern timezone, COUNT(TimeZone) will be 3, whereas COUNT(DISTINCT TimeZone) will be 1.