List:General Discussion« Previous MessageNext Message »
From:Jon Forsyth Date:October 11 2011 6:26pm
Subject:Null Output Problem
View as plain text  
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

Thread
Null Output ProblemJon Forsyth11 Oct
  • Re: Null Output Problemhsv11 Oct