CODEWITHSUNDEEP
sqlsql-servercount
Azazel
Azazel

Reputation: 23

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 looks 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).

Upvotes: 0

Views: 196

Answers (2)

Hector Sanchez
Hector Sanchez

Reputation: 2317

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

Upvotes: 0

CristiC
CristiC

Reputation: 22698

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.

Upvotes: 1

Related Questions