IS it possible to count...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SF - 21 Nov 2007 01:30 GMT

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

Reply to this Message

John Nurick - 21 Nov 2007 07:08 GMT

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);

>Hi,
>
[quoted text clipped - 19 lines]
>
>SF

--
John Nurick - Access MVP

Reply to this Message