Question:
I have a table that stores test answers. It looks something like this:
Student Date_taken Q1 Q2 Q3 ... Q78
If each question (Q%) is true/false (ENUM(”,’T’,’F’)), how do I structure a select statement to tally the total number of “trues” for each student on a particular test?
Answer:
SQL is a set-oriented language, so calculations on columns in a single row are difficult. I took a simplified version of your question and created a table like this:
StudentID int,Q1 char(1),Q2 char(1),Q3 char(3)
With a data set like this:
StudentID Q1 Q2 Q31 T T F2 F F F3 T T T
The query below takes the columnar data and creates a row for each quarter in a temporary table:
CREATE PROCEDURE CalcGrades ASSET NOCOUNT ONDECLARE @StudentID int, @Q1 char(1), @Q2 char(1), @Q3 char(1)
Create a cursor from the original table:
DECLARE c CURSOR FOR SELECT *FROM GradesORDER BY StudentIDCREATE TABLE #Grades (IndexCol int identity (0,1), StudentID int,
Quarter char(2), TrueFalse char(1))OPEN cFETCH NEXT FROM cINTO @StudentID, @Q1, @Q2, @Q3 WHILE @@FETCH_STATUS = 0 BEGIN INSERT #Grades (StudentID, Quarter, TrueFalse)
VALUES (@StudentID, 'Q1', @Q1) INSERT #Grades (StudentID, Quarter, TrueFalse)
VALUES (@StudentID, 'Q2', @Q2) INSERT #Grades (StudentID, Quarter, TrueFalse)
VALUES (@StudentID, 'Q3', @Q3) FETCH NEXT FROM c INTO @StudentID, @Q1, @Q2, @Q3 ENDSELECT StudentID, Trues=Count(CASE WHEN TrueFalse=
'T' THEN 1 END), Falses=Count(CASE WHEN TrueFalse= 'F' THEN 1 END)FROM #GradesGROUP BY StudentIDORDER BY StudentIDCLOSE cDEALLOCATE c RETURN 0
Then you can do a select with the aggregate function COUNT(), which will produce this result:
StudentID Trues Falses ----------- ----------- ----------- 1 2 12 0 33 3 0
Addendum:
The person who posted this question to me in the first place sent me a query based on this one that creates the same results, but without using a cursor. It looks like this:
SELECT StudentID, (CASE Q1 WHEN 'T' THEN 1 ELSE 0 END)+ (CASE Q2 WHEN 'T' THEN 1 ELSE 0 END)+ (CASE Q3 WHEN 'T' THEN 1 ELSE 0 END) AS Trues , (CASE Q1 WHEN 'F' THEN 1 ELSE 0 END)+ (CASE Q2 WHEN 'F' THEN 1 ELSE 0 END)+ (CASE Q3 WHEN 'F' THEN 1 ELSE 0 END) AS Falses FROM Grades
