re: Re: aggregate functions producing bad data
Jay, Tuesday, September 17, 2002, 10:45:51 PM, you wrote: J> On my server the MySQL database decided to die on me. So I killed it and J> restarted and that didn't work. So I just rebooted the machine. Now I'm J> getting this error below. J> Invalid SQL: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1 J> mysql error: Can't open file: 'privatemessage.MYD'. (errno: 145) J> mysql error number: 1016 J> Anyone know what is going on? 145 = Table was marked as crashed and should be repaired http://www.mysql.com/doc/en/Repair.html Repair table with myisamchk or REPAIR TABLE: http://www.mysql.com/doc/en/REPAIR_TABLE.html http://www.mysql.com/doc/en/myisamchk_repair_options.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: aggregate functions producing bad data
Perhaps you should create a new table with an id column and a flag column. Select the found calls > 5 or <= 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:[EMAIL PROTECTED]] > Sent: September 17, 2002 21:00 > To: [EMAIL PROTECTED] > Subject: aggregate functions producing bad data > > > My query: > > SELECT > count(id), > count( IF(call_end - call_start > 5, id, NULL) ), > count( IF(call_end - call_start <= 5, id, NULL) ) > FROM > t_calllog_calls; > > My result: > > 1994 > 1956 > 35 > > However, 1956 + 35 != 1994. > > Running MySQL 3.23.49-nt > > Cheers, > Rich > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: aggregate functions producing bad data
Hi. Could you run the following query: SELECTIF(call_end - call_start > 5, id, NULL) AS long, IF(call_end - call_start <= 5, id, NULL) AS short, COUNT(*) FROM t_calllog_calls GROUP BY long, short Given that I have not made any mistake, that should list all rows which do not "behave" as expected for whatever reason. From your description, you expect there to be two rows: longshort 0 non-0 non-0 0 But you should get some additional row(s), which account for the difference you observe. Once you know in which way the condition fails (e.g. both comparisons resulting in NULL), you can search for these rows: SELECTIF(call_end - call_start > 5, id, NULL) AS long, IF(call_end - call_start <= 5, id, NULL) AS short, * FROM t_calllog_calls HAVINGISNULL(short) AND ISNULL(long) (of course you may have to change this accordingly). One of may best bets would be that call_end and call_start are of some data type for which your comparison results in NULL due to some illegal value or because the comparison is not defined or whatever. Please report any findings you get. Regards, Benjamin. PS: People, please learn to quote. There is no reason to quote 60 lines of mail footers! On Tue 2002-09-17 at 12:37:59 -0700, [EMAIL PROTECTED] wrote: > > > > My query: > > > > > > > > SELECT > > > > count(id), > > > > count( IF(call_end - call_start > 5, id, NULL) ), > > > > count( IF(call_end - call_start <= 5, id, NULL) ) > > > > FROM > > > > t_calllog_calls; > > > > > > > > My result: > > > > > > > > 1994 > > > > 1956 > > > > 35 > > > > > > > > However, 1956 + 35 != 1994. > > > > > > > > Running MySQL 3.23.49-nt [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: aggregate functions producing bad data
Is call_end - call_start a integer, maybe the range is to big and it is eval'd as NULL or false. shrug. On Tue, 17 Sep 2002, Gelu Gogancea wrote: > Hi, > > One thing I'd check: what happens if t_calllog_calls.id is NULL? > ...is not count. > > Regards, > > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > - Original Message - > From: "Brian Reichert" <[EMAIL PROTECTED]> > To: "Richard Unger" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Tuesday, September 17, 2002 10:16 PM > Subject: Re: aggregate functions producing bad data > > > > On Tue, Sep 17, 2002 at 11:59:42AM -0700, Richard Unger wrote: > > > My query: > > > > > > SELECT > > > count(id), > > > count( IF(call_end - call_start > 5, id, NULL) ), > > > count( IF(call_end - call_start <= 5, id, NULL) ) > > > FROM > > > t_calllog_calls; > > > > > > My result: > > > > > > 1994 > > > 1956 > > > 35 > > > > > > However, 1956 + 35 != 1994. > > > > > > Running MySQL 3.23.49-nt > > > > > > > Cheers, > > > Rich > > > > -- > > Brian 'you Bastard' Reichert <[EMAIL PROTECTED]> > > 37 Crystal Ave. #303 Daytime number: (603) 434-6842 > > Derry NH 03038-1713 USA Intel architecture: the left-hand path > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: aggregate functions producing bad data
...or you can try : SELECT count(*), count( IF(call_end - call_start > 5, id, NULL) ), count( IF(call_end - call_start <= 5, id, NULL) ) FROM t_calllog_calls; Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Brian Reichert" <[EMAIL PROTECTED]> To: "Richard Unger" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, September 17, 2002 10:16 PM Subject: Re: aggregate functions producing bad data > On Tue, Sep 17, 2002 at 11:59:42AM -0700, Richard Unger wrote: > > My query: > > > > SELECT > > count(id), > > count( IF(call_end - call_start > 5, id, NULL) ), > > count( IF(call_end - call_start <= 5, id, NULL) ) > > FROM > > t_calllog_calls; > > > > My result: > > > > 1994 > > 1956 > > 35 > > > > However, 1956 + 35 != 1994. > > > > Running MySQL 3.23.49-nt > > One thing I'd check: what happens if t_calllog_calls.id is NULL? > > > Cheers, > > Rich > > -- > Brian 'you Bastard' Reichert <[EMAIL PROTECTED]> > 37 Crystal Ave. #303 Daytime number: (603) 434-6842 > Derry NH 03038-1713 USA Intel architecture: the left-hand path > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: aggregate functions producing bad data
Hi, > One thing I'd check: what happens if t_calllog_calls.id is NULL? ...is not count. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Brian Reichert" <[EMAIL PROTECTED]> To: "Richard Unger" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, September 17, 2002 10:16 PM Subject: Re: aggregate functions producing bad data > On Tue, Sep 17, 2002 at 11:59:42AM -0700, Richard Unger wrote: > > My query: > > > > SELECT > > count(id), > > count( IF(call_end - call_start > 5, id, NULL) ), > > count( IF(call_end - call_start <= 5, id, NULL) ) > > FROM > > t_calllog_calls; > > > > My result: > > > > 1994 > > 1956 > > 35 > > > > However, 1956 + 35 != 1994. > > > > Running MySQL 3.23.49-nt > > > > Cheers, > > Rich > > -- > Brian 'you Bastard' Reichert <[EMAIL PROTECTED]> > 37 Crystal Ave. #303 Daytime number: (603) 434-6842 > Derry NH 03038-1713 USA Intel architecture: the left-hand path > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: aggregate functions producing bad data
On my server the MySQL database decided to die on me. So I killed it and restarted and that didn't work. So I just rebooted the machine. Now I'm getting this error below. Invalid SQL: SELECT COUNT(*) AS messages FROM privatemessage WHERE userid=1 mysql error: Can't open file: 'privatemessage.MYD'. (errno: 145) mysql error number: 1016 Anyone know what is going on? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: aggregate functions producing bad data
mysql> Select count(id) from t_calllog_calls where id IS NULL; +---+ | count(id) | +---+ | 0 | +---+ 1 row in set (0.00 sec) > -Original Message- > From: mtoth [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 17, 2002 12:39 PM > To: Richard Unger > Cc: [EMAIL PROTECTED] > Subject: RE: aggregate functions producing bad data > > > what does > Select count(id) from t_calllog_calls where id IS NULL > give you? > > > On Tue, 17 Sep 2002, Richard Unger wrote: > > > That would just give me 1994 for all 3 columns of output. > What I want to know is how many calls were longer than 5 > seconds, how many were <= 5 seconds, and the total number of calls. > > > > Cheers, > > Rich > > > > > > > -Original Message- > > > From: Gelu Gogancea [mailto:[EMAIL PROTECTED]] > > > Sent: Tuesday, September 17, 2002 12:08 PM > > > To: Richard Unger; [EMAIL PROTECTED] > > > Subject: Re: aggregate functions producing bad data > > > > > > > > > Hi, > > > I think you should try to use 0 instead of NULL > > > > > > Regards, > > > > > > Gelu > > > _ > > > G.NET SOFTWARE COMPANY > > > > > > Permanent e-mail address : [EMAIL PROTECTED] > > > [EMAIL PROTECTED] > > > - Original Message - > > > From: "Richard Unger" <[EMAIL PROTECTED]> > > > To: <[EMAIL PROTECTED]> > > > Sent: Tuesday, September 17, 2002 9:59 PM > > > Subject: aggregate functions producing bad data > > > > > > > > > My query: > > > > > > SELECT > > > count(id), > > > count( IF(call_end - call_start > 5, id, NULL) ), > > > count( IF(call_end - call_start <= 5, id, NULL) ) > > > FROM > > > t_calllog_calls; > > > > > > My result: > > > > > > 1994 > > > 1956 > > > 35 > > > > > > However, 1956 + 35 != 1994. > > > > > > Running MySQL 3.23.49-nt > > > > > > Cheers, > > > Rich > > > > > > > - > > > Before posting, please check: > > >http://www.mysql.com/manual.php (the manual) > > >http://lists.mysql.com/ (the list archive) > > > > > > To request this thread, e-mail > <[EMAIL PROTECTED]> > > > To unsubscribe, e-mail > > > <[EMAIL PROTECTED]> > > > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > > > > > > > > > > > > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: > http://lists.mysql.com/php/unsubscribe.php > > > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: aggregate functions producing bad data
> -Original Message- > From: Dan Nelson [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 17, 2002 12:31 PM > To: Brian Reichert > Cc: Richard Unger; [EMAIL PROTECTED] > Subject: Re: aggregate functions producing bad data > > > In the last episode (Sep 17), Brian Reichert said: > > On Tue, Sep 17, 2002 at 11:59:42AM -0700, Richard Unger wrote: > > > My query: > > > > > > SELECT > > > count(id), > > > count( IF(call_end - call_start > 5, id, NULL) ), > > > count( IF(call_end - call_start <= 5, id, NULL) ) > > > FROM > > > t_calllog_calls; > > > > > > My result: > > > > > > 1994 > > > 1956 > > > 35 > > > > > > However, 1956 + 35 != 1994. > > > > > > Running MySQL 3.23.49-nt > > > > One thing I'd check: what happens if t_calllog_calls.id is NULL? > > Also check if call_end or call_start are NULL; that will > cause both IFs > to fail. Don't you love NULLs :) > Ooh, good suggestion. I hadn't thought of that! ...but no, none of the call_start or call_end fields are NULL. Yeah, I love nulls :) Cheers, Rich - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: aggregate functions producing bad data
what does Select count(id) from t_calllog_calls where id IS NULL give you? On Tue, 17 Sep 2002, Richard Unger wrote: > That would just give me 1994 for all 3 columns of output. What I want to know is >how many calls were longer than 5 seconds, how many were <= 5 seconds, and the total >number of calls. > > Cheers, > Rich > > > > -Original Message- > > From: Gelu Gogancea [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, September 17, 2002 12:08 PM > > To: Richard Unger; [EMAIL PROTECTED] > > Subject: Re: aggregate functions producing bad data > > > > > > Hi, > > I think you should try to use 0 instead of NULL > > > > Regards, > > > > Gelu > > _ > > G.NET SOFTWARE COMPANY > > > > Permanent e-mail address : [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > - Original Message - > > From: "Richard Unger" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Tuesday, September 17, 2002 9:59 PM > > Subject: aggregate functions producing bad data > > > > > > My query: > > > > SELECT > > count(id), > > count( IF(call_end - call_start > 5, id, NULL) ), > > count( IF(call_end - call_start <= 5, id, NULL) ) > > FROM > > t_calllog_calls; > > > > My result: > > > > 1994 > > 1956 > > 35 > > > > However, 1956 + 35 != 1994. > > > > Running MySQL 3.23.49-nt > > > > Cheers, > > Rich > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: aggregate functions producing bad data
In the last episode (Sep 17), Brian Reichert said: > On Tue, Sep 17, 2002 at 11:59:42AM -0700, Richard Unger wrote: > > My query: > > > > SELECT > > count(id), > > count( IF(call_end - call_start > 5, id, NULL) ), > > count( IF(call_end - call_start <= 5, id, NULL) ) > > FROM > > t_calllog_calls; > > > > My result: > > > > 1994 > > 1956 > > 35 > > > > However, 1956 + 35 != 1994. > > > > Running MySQL 3.23.49-nt > > One thing I'd check: what happens if t_calllog_calls.id is NULL? Also check if call_end or call_start are NULL; that will cause both IFs to fail. Don't you love NULLs :) -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: aggregate functions producing bad data
id is the primary key. There are no entries in the DB where id=NULL Cheers, Rich > -Original Message- > From: Brian Reichert [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 17, 2002 12:16 PM > To: Richard Unger > Cc: [EMAIL PROTECTED] > Subject: Re: aggregate functions producing bad data > > > On Tue, Sep 17, 2002 at 11:59:42AM -0700, Richard Unger wrote: > > My query: > > > > SELECT > > count(id), > > count( IF(call_end - call_start > 5, id, NULL) ), > > count( IF(call_end - call_start <= 5, id, NULL) ) > > FROM > > t_calllog_calls; > > > > My result: > > > > 1994 > > 1956 > > 35 > > > > However, 1956 + 35 != 1994. > > > > Running MySQL 3.23.49-nt > > One thing I'd check: what happens if t_calllog_calls.id is NULL? > > > Cheers, > > Rich > > -- > Brian 'you Bastard' Reichert <[EMAIL PROTECTED]> > 37 Crystal Ave. #303 Daytime number: (603) 434-6842 > Derry NH 03038-1713 USA Intel > architecture: the left-hand path > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: aggregate functions producing bad data
That would just give me 1994 for all 3 columns of output. What I want to know is how many calls were longer than 5 seconds, how many were <= 5 seconds, and the total number of calls. Cheers, Rich > -Original Message- > From: Gelu Gogancea [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, September 17, 2002 12:08 PM > To: Richard Unger; [EMAIL PROTECTED] > Subject: Re: aggregate functions producing bad data > > > Hi, > I think you should try to use 0 instead of NULL > > Regards, > > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > - Original Message - > From: "Richard Unger" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Tuesday, September 17, 2002 9:59 PM > Subject: aggregate functions producing bad data > > > My query: > > SELECT > count(id), > count( IF(call_end - call_start > 5, id, NULL) ), > count( IF(call_end - call_start <= 5, id, NULL) ) > FROM > t_calllog_calls; > > My result: > > 1994 > 1956 > 35 > > However, 1956 + 35 != 1994. > > Running MySQL 3.23.49-nt > > Cheers, > Rich > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: aggregate functions producing bad data
I _thought_ it was the fault of the aggregate function, but now I'm really confused! When I produce them as 3 separate queries, I still get numbers that don't add up: mysql> select count(id) from t_calllog_calls; +---+ | count(id) | +---+ | 1994 | +---+ 1 row in set (0.00 sec) mysql> select count(id) from t_calllog_calls where call_end - call_start > 5; +---+ | count(id) | +---+ | 1956 | +---+ 1 row in set (0.00 sec) mysql> select count(id) from t_calllog_calls where call_end - call_start <= 5; +---+ | count(id) | +---+ |35 | +---+ -Original Message- From: Richard Unger Sent: Tuesday, September 17, 2002 12:00 PM To: '[EMAIL PROTECTED]' Subject: aggregate functions producing bad data My query: SELECT count(id), count( IF(call_end - call_start > 5, id, NULL) ), count( IF(call_end - call_start <= 5, id, NULL) ) FROM t_calllog_calls; My result: 1994 1956 35 However, 1956 + 35 != 1994. Running MySQL 3.23.49-nt Cheers, Rich - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: aggregate functions producing bad data
Hi, I think you should try to use 0 instead of NULL Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: "Richard Unger" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, September 17, 2002 9:59 PM Subject: aggregate functions producing bad data My query: SELECT count(id), count( IF(call_end - call_start > 5, id, NULL) ), count( IF(call_end - call_start <= 5, id, NULL) ) FROM t_calllog_calls; My result: 1994 1956 35 However, 1956 + 35 != 1994. Running MySQL 3.23.49-nt Cheers, Rich - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: aggregate functions producing bad data
On Tue, Sep 17, 2002 at 11:59:42AM -0700, Richard Unger wrote: > My query: > > SELECT > count(id), > count( IF(call_end - call_start > 5, id, NULL) ), > count( IF(call_end - call_start <= 5, id, NULL) ) > FROM > t_calllog_calls; > > My result: > > 1994 > 1956 > 35 > > However, 1956 + 35 != 1994. > > Running MySQL 3.23.49-nt One thing I'd check: what happens if t_calllog_calls.id is NULL? > Cheers, > Rich -- Brian 'you Bastard' Reichert<[EMAIL PROTECTED]> 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
