GROUP BY function giving inaccurate Count figures 1

[MustangPriMe]

I've apparently forgotten how to use the GROUP BY clause effectively.

I have 3 tables relating to each other - tblCompanies, tblSites and tblContacts.

tblCompanies
- fldCompanyID
- fldCompanyName
- etc

tblSites
- fldSiteID
- fldOwningCompanyID
- fldAddress
- etc

tblContacts
- fldContactID
- fldOwningSiteID
- fldName
- fldContactArchived
- etc

I'm trying to query the database to list how many sites per company have non-archived (or live) contacts

I'm using..

SELECT tblSites.fldOwningCompanyID, Count(tblSites.fldSiteID) AS CountOffldSiteID
FROM tblContacts INNER JOIN tblSites ON tblContacts.fldOwningSiteID = tblSites.fldSiteID
WHERE (tblContacts.fldContactArchived=0)
GROUP BY tblSites.fldOwningCompanyID;

.. which returns a company ID per row, but gives the count of live contacts for each company instead of the number of sites which have live contacts for each company.

I can't think where I'm going wrong. The only thing I can think is that it's something to do with the fact that the tables are actually linked tables from an SQL database, and for some reason the GROUP BY clause is not working in the same way.
Can anyone put me out of my misery?

 

[PHV]

JetSQL lacks the Count(DISTINCT ...) aggregate function.
A workaround:
SELECT S.fldOwningCompanyID, Count(*) AS CountOffldSiteID
FROM tblSites AS S INNER JOIN (
SELECT DISTINCT fldOwningSiteID FROM tblContacts WHERE fldContactArchived=0
) AS D ON S.fldSiteID = D.fldOwningSiteID
GROUP BY S.fldOwningCompanyID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MustangPriMe]

Excellent! Have a star!

I'm not sure how I haven't come across this before.
Do you know the reason for this behaviour? Is it because I have a linked table 'below' the one I'm trying to Group by?

Thanks
Paul

 

[PHV]

The explanation is in the first line of my previous reply.
In ANSI SQL you may simply do this:
SELECT S.fldOwningCompanyID, COUNT(DISTINCT C.fldOwningSiteID) AS CountOffldSiteID
FROM tblSites AS S
INNER JOIN tblContacts AS C ON S.fldSiteID = C.fldOwningSiteID
WHERE C.fldContactArchived = 0
GROUP BY S.fldOwningCompanyID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886