IS it possible to count...

[SF]

Hi,

I have the following result set...

District Commune
D1 C1
D1 C2
D2 C1
D3 C1

I want to count how many district and commune, so I create a query below:

SELECT Count(tblDistricts.Di_District_e) AS CountOfDi_District_e,
Count(tblCommunes.Cn_Commune_e) AS CountOfCn_Commune_e
FROM tblDistricts INNER JOIN tblCommunes ON tblDistricts.Di_DistrictID =
tblCommunes.Cn_DistrictID;


I found that # of district and # of commune are equal (which is wrong
because there is onle 3 dsitricts and 4 communes)

Could someone advice?

SF

 

[John Nurick]

Hi SF,

I've used the Customers table in the Northwind sample database as a
'stand-in', with Countries corresponding to Districts and Cities to
Communes.

This gets you the number of countries:
SELECT COUNT(*) As NumCountries
FROM (SELECT DISTINCT Country FROM Customers);
and this the number of cities:
SELECT COUNT(*) As NumCities
FROM (SELECT DISTINCT City, Country FROM Customers);