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.

Customer
Id      Name
101      Name1
102      Name2
103      Name3
…      …



CustomerVisit
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.
quasar_eeAsked:
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

0
 
awking00Commented:
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?
0
 
awking00Commented:
If the desired count is 2, just modify Ryan Chong's query to -
select a.id, a.name, count(distinct(VisitedCity)) VisitedCityCount
0
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.