Find users who visit only one city instead of multiple cities in MySQL [duplicate]

Question

I have a table with user ID, city and booking date. I need to find out the users who visited only one city and not multiple cities in a date range.

SELECT
user_id,
city,
COUNT(*) as result
FROM visits
WHERE start_ts >= 1675209600 -- 1675209600 = 01.02.2023 00:00
AND end_ts <= 1676419200 -- 1676419200 = 15.2.2023 00:00
GROUP BY user_id, city

user_id	city	result
10	Munich	1
11	Barcelona	2
11	Berlin	1
12	Barcelona	1
13	Prague	2
11	Barcelona	1
13	Berlin	1

User IDs 10 and 12 only visited one city. For example, user ID 11 visited Barcelona twice.

I have tried using NOT EXISTING and INNER JOIN. How to get users who only visited one city?

Answer 1

Please add HAVING condition to your query:

SELECT user_id, city, COUNT(*) as result
FROM visits 
WHERE start_ts >= 1675209600 -- 1675209600 = 01.02.2023 00:00
AND end_ts <= 1676419200 -- 1676419200 = 15.2.2023 00:00
GROUP BY user_id, city
HAVING result = 1

If you need to count only the number of unique cities, you can use COUNT(DISTINCT(city)) and remove them from the grouping like this:

SELECT user_id, city, COUNT(DISTINCT(city)) as result
FROM visits 
WHERE start_ts >= 1675209600 -- 1675209600 = 01.02.2023 00:00
AND end_ts <= 1676419200 -- 1676419200 = 15.2.2023 00:00
GROUP BY user_id
HAVING result = 1