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=arch...@jab.org
