Problem statement-
I have two tables 'Location' and 'Visit'. In the table 'Location' I have details about the city,country,zipcode etc and in the 'Visit' I have details of how many visits were done to the locations mentioned in the 'LOCATION' table.
Below are the column values of the two tables -
ID|City Name|Country Name|Zip Code|
ID|Visit Date|Visit End Date|Visit Type|
Question- I want to calculate the total number of visits done for a particular country and for a particular visit type(based on the value of visit type column in Visit table). Typically this would be straight forward by taking the total number of rows in visit table and using the 'ID' column as the key to join them. In addition to this I want the results to be displayed as follows-
##Results
Country Name|Total no of Visits|
This is what's gotten me in a bind. I am not sure how I can get the country name here along with the count value of the rows.
Understanding that the ID is the common attribute to join both tables, you could do it with a simple group by operation...
select
[Country Name],
count(*)
from location l
join visit v
on l.id = v.id
group by [Country Name]
If you want to split the count by Country Name and Visit Type, you must add it to the query and group also by this field, but the output won't be as you describe
select
[Country Name],
[Visit Type],
count(*)
from location l
join visit v
on l.id = v.id
group by [Country Name],[Visit Type]