Using Subqueries to join 2 more tables which do not have distinct values in a column

We want to find the number of Flight bookings by Country, number of Car bookings by Country and the number of Hotel bookings by Country all appearing in one table with same country on one row.
We want to join the country field from all the 3 tables. Flight table, CarHire table, and Hotel Booking table
We will use subquery to get the country from each table and we will count each row in each table as the respective product booking  and then group by the country.
In that way, we will get distinct countries and their respective number of  bookings for each country

select 
		* 
from 
			(
					select 
							country
							,count(*) as [# Flights Bookings] 
					FROM 
							[Antrak].[dbo].[Flights]
					group by country
			) as flights

left join 

			/*get distinct countries and number of hotel bookings */
			 (select 
					country, 
					count(*) as [# Hotel Bookings]  
			FROM 
				[Antrak].[dbo].[Hotels]
			group by 
					country
			) as hotels

on flights.Country = hotels.Country

left join 

			(
						select 
								country, 
								count(*) as [# Car Bookings]  
						FROM 
								[Antrak].[dbo].[CarHires]
						group by 
								country
					) as carhires

on flights.Country = carhires.Country
order by flights.Country

We used a left join because, we want all countries in the Flight table and if a country in the Flight table does not exist in either the Hotel or Car Booking table
then it will mean there would not have been any hotel booking or car booking for that country

The resulting table will be as below:

People Who Read The Above Post Also Read This:

Rank a Place Visited By a Customer- Microsoft SQL Server – T-Transact SQL Statement Query Clause Evaluation Order – Which Part of SQL Statements Execute First and in What Order? Get Numbers and Percentages from Tables – Microsoft Sql Server Populating Superset Caravel MySQL database tables with Data via Heroku