From: Date: October 2 2008 3:36am
Subject: Re: discrepancy in numbers from SELECT COUNT(DISTINCT ...) and SELECT DISTINCT
List-Archive: http://lists.mysql.com/ug-melbourne/9
Message-Id: <70b2ff110810011836s8291448vb0602aa4d489d4bd@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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
>