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