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?

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

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS