CODEWITHSUNDEEP
sqlmysqlsql-serverdatabaseoracle-database
Zak
Zak

Reputation: 7077

Need help writing a SQL query

I have the following tables.

Customer_table
Customer_ID | Last name | First Name | City_ID | Region_ID | Country_ID

Country_table
Country_ID | Country

Region_table
Region_ID | Region | Country_ID

City_table
City_ID | City | Region_ID | Country_ID

I need to find the number of customers city-wise,ie, for all countries, for all regions in the countries, for all cities in the region, i need to get the no. of customers in each city.

For example, I wrote the following SQL to get the no. of customers in each country:

SELECT a.country_id , b.country,count(*) 
FROM Customer_Table a INNER JOIN Country_Table b
ON a.country_id = b.country_id
GROUP BY b.country , b.country_id ;

and to get the no. of customers in each region for a particular country:

SELECT a.region_id , b.region , count(*)
FROM Customers_table a INNER JOIN Region_Table b
ON a.region_id = b.region_id
WHERE a.country_id ='USA' AND b.country_id ='USA'
GROUP BY region_id , country ;

What I need to find is the number of customers city-wise,ie, for all countries, for all regions in the countries, for all cities in the region, i need to get the no. of customers in each city, something like 

Country | Region  |       City     | no.of customers
  USA   |   CA    |  San Francisco |    53
  USA   |   CA    |  Los Angeles   |    45
  USA   |   CA    |  Sacramento    |    16
  USA   |   WA    |  Seattle       |    46
  USA   |   WA    |  Olympia       |    9
  UK    | England |  London        |    89
  UK    | England |  Nottingham    |    71
  UK    | Wales   |  Cardiff       |    69
  UK    | Wales   |  Newport       |    23

  ..................and so on for other countries.

Upvotes: 1

Views: 2807

Answers (3)

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32760

For SQL Server:

SELECT Country, Region, City, COUNT(Customer_ID) as [no.of customers]
FROM Country_Table
INNER JOIN Region_Table ON Country_Table.Country_ID = Region_Table.Country_ID
INNER JOIN City_Table ON City_Table.Region_ID = Region_Table.Region_ID
INNER JOIN Customer_Table ON Customer_Table.City_ID = City_Table.City_ID
GROUP BY Country, Region, City;

Upvotes: 2

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196306

Your tables have redundant info

since they are linked, each table only needs to reference its direct parent and not all parents in the hierarchy ..

so customer only needs to reference the city, city only the region, region only the country

SELECT
    Country_Table.Country,
    Region_Table.Region,
    City_Table.City,
    Count(Customer_Id) AS Customers
FROM
    Country_Table
    INNER JOIN Region_Table ON Region_Table.Country_Id = Country_Table.Country_Id
    INNER JOIN City_Table ON City_Table.Region_Id = Region_Table.Region_Id
    INNER JOIN Customer_Table ON Customer_Table.City_Id = City_Table.City_Id
GROUP BY
    Country_Table.Country,
    Region_Table.Region,
    City_Table.City

Upvotes: 4

Pranay Rana
Pranay Rana

Reputation: 176956

Try this :

select Country,Region,City,count(Customer_ID)
from Customer_table as cust
inner join  Country_table on Country_ID=cust.Country_ID
inner join Region_table on Region_ID=cust.Region_ID
inner join Region_table on City_ID=cust.City_ID
group by country,Region,City

Upvotes: 2

Related Questions