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
