If your condition that the status field only contains two values holds true, then there should be no difference in the two queries you have below -- that I can see, unless someone can prove me blind...
Are you *absolutely* sure none of the columns contain extra spaces, nulls, etc...? You might try changing your where condition to WHERE status LIKE \"%published%\" for the first and WHERE status LIKE \"%input%\" for the second -- just to see if there are any other funky characters causing problems. If the modified where conditions return the correct results, there's something wrong with the data. --jeff ----- Original Message ----- From: "John Hughes" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, March 02, 2002 12:32 AM Subject: Query structure puzzle > I have a table with approximately 600 rows. Two of the fields are > "record_date" and "status" Every record has an entry in the > "record_date" in 0000-00-00 format. The "status" is either "input" > or "published" (I DID NOT create this colum as ENUM; those are simply > the only values my PHP interface provides.) > > What I want to do is count the number of "published" letters grouped > by date. We publish letters seven days a week, so there are no days > when there would be no "published" letters. > > So, here is the puzzle. These two queries serve up dramatically > different results. The only difference in the query is the WHERE > statement. > > $sql = "SELECT record_date, COUNT(status) AS number_of_letters > FROM $table_name > WHERE status = \"published\" > GROUP BY record_date > "; > > > $sql = "SELECT record_date, COUNT(status) AS number_of_letters > FROM $table_name > WHERE status <> \"input\" > GROUP BY record_date > "; > > The query WHERE status = \"published\" finds 17 rows. > > The query WHERE status <> \"input\" finds 33 rows. > > The 33 row answer is the correct answer. I checked the individual > records and can find no record with an answer other than "input" or > "published" in the status. > > ASSUMING all records are either "input" or "published" and all > records have valid record_date, WHY does <> status give a different > result than = status? > > John Hughes > > RESULTS of WHERE status = "published" > Date Number > 2002-01-30 10 > 2002-01-31 12 > 2002-02-01 8 > 2002-02-02 30 > 2002-02-03 8 > 2002-02-04 1 > 2002-02-05 8 > 2002-02-06 10 > 2002-02-07 9 > 2002-02-08 19 > 2002-02-09 30 > 2002-02-10 8 > 2002-02-11 10 > 2002-02-12 10 > 2002-02-16 2 > 2002-02-27 1 > 2002-03-03 8 > > > RESULTS of WHERE status <> "input" > Date Number > 2002-01-30 10 > 2002-01-31 12 > 2002-02-01 8 > 2002-02-02 30 > 2002-02-03 8 > 2002-02-04 11 > 2002-02-05 8 > 2002-02-06 10 > 2002-02-07 9 > 2002-02-08 20 > 2002-02-09 30 > 2002-02-10 8 > 2002-02-11 10 > 2002-02-12 10 > 2002-02-13 11 > 2002-02-14 9 > 2002-02-15 9 > 2002-02-16 33 > 2002-02-17 11 > 2002-02-18 9 > 2002-02-19 9 > 2002-02-20 19 > 2002-02-21 10 > 2002-02-22 9 > 2002-02-23 32 > 2002-02-24 8 > 2002-02-25 19 > 2002-02-27 10 > 2002-02-28 9 > 2002-03-01 9 > 2002-03-02 29 > 2002-03-03 8 > 2002-03-04 10 > > > __________________________________________________ > Do You Yahoo!? > Yahoo! Sports - sign up for Fantasy Baseball > http://sports.yahoo.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 > --------------------------------------------------------------------- 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
