List:Melbourne MySQL Users Group« Previous MessageNext Message »
From:deepfryed Date:October 2 2008 1:36am
Subject:Re: discrepancy in numbers from SELECT COUNT(DISTINCT ...) and SELECT DISTINCT
View as plain text  
a work mate helped me figure this one out,

COUNT(DISTINCT Email, Suburb, State) will ignore any tuples where one
of those columns is NULL.

I always thought that the ignore NULL holds only for single column
COUNT(..), but apparently not.

The workarounds are to wrap every column in a COALESCE or use
sub-selects and do a COUNT(*) on the result set.


On 10/2/08, bharanee rathna <deepfryed@stripped> wrote:
> Hi,
>
> I have a peculiar issue where
>
> SELECT DISTINCT Email, Suburb, State FROM Users  WHERE ( Email = '
> test@stripped' );
>
> gives me
> *14565* rows in set (0.20 sec)
>
> but the same query written as
>
> SELECT COUNT(DISTINCT Email, Suburb, State) FROM Users  WHERE ( Email = '
> test@stripped' );
>
> gives me
> +------------------------------------------------+
> | count(DISTINCT Email, Suburb, State)           |
> +------------------------------------------------+
> |                                          *12616* |
> +------------------------------------------------+
> 1 row in set (0.12 sec)
>
> Not sure if I'm missing something obvious here, I'd appreciate if someone
> can shed light on this strange behavior.
>
> I'm using Server version: 5.0.22-Debian_3
>
> Thanks
> Bharanee
>
Thread
discrepancy in numbers from SELECT COUNT(DISTINCT ...) and SELECT DISTINCTbharanee rathna2 Oct
  • Re: discrepancy in numbers from SELECT COUNT(DISTINCT ...) and SELECT DISTINCTdeepfryed2 Oct