| Downloads |
|---|
| 26965.zip |
I wrote the statement that Listing 3 shows, which contains the results of participant answers to quiz questions in a bit column named correct. In Listing 3's sample data, 0.75 (75 percent) of participant A's answers and 0.50 (50 percent) of B's answers are correct. How can I write a query that calculates what percentage of each participant's answers is correct?
The value 1 represents a correct answer and 0 an incorrect answer, so the percentage of correct answers is actually the same as the average of the values in the correct column. Therefore, you ought to be able to average the column values. However, when you try to run the following query to average the values for each participant ID:
SELECT participantid, AVG(correct) AS avg_correct FROM QuizResults GROUP BY participantid
you receive an error message saying that an aggregate function can't take a bit data type as an argument. In attempting to avoid the error, you might try to convert the bit value to an integer data type, as the following code demonstrates:
SELECT participantid, AVG(CAST(correct AS int)) AS avg_correct FROM QuizResults GROUP BY participantid
Although the query doesn't generate an error, it results in another problem. Both A and B return 0 because the AVG() function returns a value with the same data type as its argument, which in this case is an integer. SQL Server truncates integers, so when you convert the numeric values 0.75 and 0.50 into integers, it truncates them to 0. For example, when you run the following code:
SELECT CAST(0.75 AS int)
the result is 0. A workable solution is to convert the correct column to a numeric data type either explicitly or implicitly. The following modified query shows an implicit conversion caused by multiplying 1. (read "one dot") by the correct column:
SELECT participantid, CAST(AVG(1. * correct) AS numeric(3, 2)) AS avg_correct FROM QuizResults GROUP BY participantid
And you should receive the desired results.
