CODEWITHSUNDEEP
sql
Andreas
Andreas

Reputation: 301

Sql query help for a beginner

Hi i need a query help (and yes i have been trying to search but could not find something that helped me)

I have two tables:

Customer

Country

How I would like to write a query to have result as following:

CountryID, Country, NumberOfOccurancesOfThisCountryInTheCustomerTable

Help would really be appreciated!

Upvotes: 1

Views: 107

Answers (2)

Yuck
Yuck

Reputation: 50845

Try this:

SELECT CountryID, Country,
  COUNT(Customer.CustomerID) AS NumberOfOccurancesOfThisCountryInTheCustomerTable
FROM Country LEFT JOIN
     Customer ON Country.CountryID = Customer.CountryID
GROUP BY Country.CountryID, Country.Country

EDIT: Using LEFT JOIN vs. INNER JOIN to include Country records that have zero Customer records (thanks Mark Bannister).

Upvotes: 6

Jim B
Jim B

Reputation: 8574

Something like this should do it, assuming CustomerId is a primary key:

SELECT 
    Country.CountryId,
    Country.Country,
    COUNT(Customers.CustomerId)
FROM 
    Country INNER JOIN Customers ON Country.CountryId = Customers.CountryId
GROUP BY 
    CountryId, Country

Upvotes: 2

Related Questions