SQL Count differs

I have a SQL query that just count and it returns the correct value, but when i slice it up and get the count for the different areas the total count no longer match the first.

This is what i got:

Table zipcodes, that contain a CountyID, CountyName & Zipcode.

Table Orders that contains ShippingCountry & ShippingZip

The first count lo开发者_运维百科oks like this:

SELECT COUNT(STFO.ShippingZip)
FROM Orders
WHERE 
AND STFO.intShippingCountryID = 202
AND STFO.ShippingZip IN (SELECT DISTINCT ZipCode FROM SwedishZipCodes07)
AND STFO.ShippingZip > 0

In one example i get this count to 3007 wich is correct. In my next count it looks more like this:

SELECT DISTINCT CountyName, CountyID,
(SELECT COUNT(STFO.ShippingZip)
FROM Orders
WHERE 
AND STFO.intShippingCountryID = 202
AND STFO.ShippingZip IN (SELECT DISTINCT ZipCode FROM SwedishZipCodes07 WHERE CountyID = ZipWrapper.CountyID)
AND STFO.ShippingZip > 0) AS Count
FROM Zipcodes ZipWrapper
ORDER BY ZipWrapper.CountyID

In the very same example i now get count like 3018. (In my examples the query is filtered more specific but they both match each other, the differences are in my simplified example code here).

You may have the same STFO.ShippingZip for 2 different CountyIDs.

That's why in the second query it will add to each CountyID and in the first one it is added only once.

Try it this way:

SELECT CountyName, CountyID,
(
    SELECT COUNT(DISTINCT(STFO.ShippingZip))
    FROM Orders STFO, SwedishZipCodes07
    WHERE STFO.intShippingCountryID = 202
    AND STFO.ShippingZip > 0
    AND STFO.ShippingZip = SwedishZipCodes07.ZipCode
    AND SwedishZipCodes07.CountyId = ZipWrapper.CountyID

) AS Count
FROM Zipcodes ZipWrapper
ORDER BY ZipWrapper.CountyID