SQL query, grouping and counting records

How would you write a query to get Customer Id, Name, VisitedCityCount from the tables below?
Should have zero if a customer not visited any city.

Id      Name
101      Name1
102      Name2
103      Name3
…      …

Id      VisitDate      VisitedCity
101      1/1/2017      City1
101      1/5/2016      City2
101      1/3/2016      City3
102      1/1/2016      City1
102      1/5/2016      City2
102      1/3/2016      City1
102      1/1/2016      City2
103      1/1/2017      City1
104      1/1/2017      City2
105      1/1/2017      City4
...     ...                     ...

Thank you in advance.
Who is Participating?
Ryan ChongCommented:
try like:

select a.id, a.name, count(VisitedCity) VisitedCityCount
from Customer a
left join CustomerVisit b
on a.id = b.id
group by a.id, a.name

Open in new window

Just so it's clear, customer id 102 has had four city visits, but to only two different cities. Which should be the VisitedCityCount, 2 or 4?
If the desired count is 2, just modify Ryan Chong's query to -
select a.id, a.name, count(distinct(VisitedCity)) VisitedCityCount
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.