From: Gerben Gieling
Date: September 18 2002 8:55am
Subject: RE: aggregate functions producing bad data
List-Archive: http://lists.mysql.com/mysql/119898
Message-Id: <3BD970ADD52E144B95B2D15070A073C03B3E64@nlnijsynth012.nl.corporate.synthon-group.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Perhaps you should create a new table with an id column and a flag
column.
Select the found calls > 5 or <=3D 5 into that new table and set the =
flag.
Then do a left join of your t_callog_calls table on the new table
selecting only the rows where the flag is NULL, these should be the rows
not found by either query.
Inspect the data in these rows manually to determine what is wrong.

> -----Original Message-----
> From: Richard Unger [mailto:runger@stripped]=20
> Sent: September 17, 2002 21:00
> To: mysql@stripped
> Subject: aggregate functions producing bad data
>=20
>=20
> My query:
>=20
> SELECT=20
> 	count(id),=20
> 	count( IF(call_end - call_start > 5, id, NULL) ),=20
> 	count( IF(call_end - call_start <=3D 5, id, NULL) )
> FROM=20
> 	t_calllog_calls;
>=20
> My result:
>=20
> 1994
> 1956
> 35
>=20
> However, 1956 + 35 !=3D 1994.
>=20
> Running MySQL 3.23.49-nt
>=20
> Cheers,
> Rich
>=20