# Counting Across a Row

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?

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

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

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.