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
>
