Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

Links

XODOX
Impressum

#1: Null Output Problem

Posted on 2011-10-11 20:26:08 by Jon Forsyth

--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--

Report this message

#2: Re: Null Output Problem

Posted on 2011-10-11 21:56:42 by (Halász Sándor) hsv

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

Report this message