Null Output Problem

--000e0cd29a7093859204af0a0a22
Content-Type: text/plain; charset=ISO-8859-1

Hello,

I have a problem with the following query:

SELECT subject_identifier, COUNT(*) FROM asr_sentence_score WHERE
total_words = correct_words GROUP BY subject_identifier;

OutPut:

+--------------------+----------+
| subject_identifier | COUNT(*) |
+--------------------+----------+
| 222 | 2 |
| 111 | 2 |
| 333 | 1 |
| 444 | 11 |
| 888 | 6 |
| 666 | 25 |
| 777 | 2 |
| 555 | 20 |
| 999 | 4 |
| 000 | 3 |
+--------------------+----------+
10 rows in set (0.00 sec)

The asr_sentence_score table is a list of test results where each row is a
single item(sentence) on the test. The subject_identifier is unique to the
test taker, and is repeated for each test item. I was using this query to
compute a count of how many items each test taker scored perfectly (total_words
= correct_words), but I realized that this excludes a test taker who did not
score perfect for any item. I want to output a '0' for those that did not
score any item perfectly. My best guess at a solution would be to revise
the WHERE clause to something like this:

WHERE [total_words = correct_words] OR [COUNT(total_words = correct_words) =
0]

but this is bad syntax. I put the brackets there for readability.

Thanks,

Jon

--000e0cd29a7093859204af0a0a22--

Re: Null Output Problem

Generally when one has this problem one joins this query with something from which one can get the whole list, something like this:

SELECT identifier, IFNULL(c, 0) AS Good, ....
(query with COUNT) AS P RIGHT JOIN table-of-identifiers ON P.identifier = table-of-identifiers.identifier

The "c" is the name given "COUNT(*)" in the query with COUNT(*).

This yields at least one row for every one in "table-of-identifiers", whether there is a match in "query with COUNT" or not; if not, "c" is NULL, and with "IFNULL" that NULL is made 0.

You write "test-taker", but for a field that could be the foregoing "identifier" your query contains only "subject_identifier", which does not look like a test-taker.

>>>> 2011/10/11 12:26 -0600, Jon Forsyth >>>>
I have a problem with the following query:

SELECT subject_identifier, COUNT(*) FROM asr_sentence_score WHERE
total_words = correct_words GROUP BY subject_identifier;

OutPut:

+--------------------+----------+
| subject_identifier | COUNT(*) |
+--------------------+----------+
| 222 | 2 |
| 111 | 2 |
| 333 | 1 |
| 444 | 11 |
| 888 | 6 |
| 666 | 25 |
| 777 | 2 |
| 555 | 20 |
| 999 | 4 |
| 000 | 3 |
+--------------------+----------+
10 rows in set (0.00 sec)

The asr_sentence_score table is a list of test results where each row is a
single item(sentence) on the test. The subject_identifier is unique to the
test taker, and is repeated for each test item. I was using this query to
compute a count of how many items each test taker scored perfectly (total_words
= correct_words), but I realized that this excludes a test taker who did not
score perfect for any item. I want to output a '0' for those that did not
score any item perfectly.
<<<<<<<<


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org