SQL query, grouping and counting records

Posted on 2018-03-08
Medium Priority
Last Modified: 2018-03-14
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.
Question by:quasar_ee
  • 2
LVL 56

Accepted Solution

Ryan Chong earned 1000 total points
ID: 42493226
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

LVL 32

Expert Comment

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?
LVL 32

Assisted Solution

awking00 earned 1000 total points
If the desired count is 2, just modify Ryan Chong's query to -
select a.id, a.name, count(distinct(VisitedCity)) VisitedCityCount

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Join & Write a Comment

SingleRun is a tool that ensures that only one instance of an application is started, running it again brings the application to focus.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question