StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODiscrepancy with "count(distinct(field))" statement in SQL query
text
Body
copied!<p>I'm running a query in SQL Server to count the number of unique email addresses that exist in our database by Australian State. However, when I try to reconcile the numbers to make sure they are right I've noticed a discrepancy which makes me think my query isn't right. Here are the queries I'm using to reconcile the numbers and the actual results:</p> <pre><code>/** Count the total number of active members (status=1) since last night **/ SELECT count(distinct(email)) Total FROM [member] WHERE status = 1 AND (created_datetime <= '2013-01-11' OR created_datetime IS NULL) /** RESULT: 8958 **/ /** Count the number of active members (status=1) who live in Victoria since last night **/ SELECT count(distinct(email)) Total FROM [member] WHERE status = 1 AND (created_datetime <= '2013-01-11' OR created_datetime IS NULL) AND [state] = 'vic' /** RESULT: 7545 **/ /** Count the number of active members (status=1) who don't live in Victoria since last night **/ SELECT count(distinct(email)) Total FROM [member] WHERE status = 1 AND (created_datetime <= '2013-01-11' OR created_datetime IS NULL) AND [state] <> 'vic' /** RESULT:1446 **/ /** Add the two results to see how they compare to the total **/ SELECT 7545+1446 /** RESULT:8991 **/ </code></pre> <p>You'll notice that the total number of distinct emails is 8958, but if you add those that live in Victoria and those that don't live in Victoria the number is 8991 which is different. Am I using the count distinct function wrongly?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload