Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jun 22, 2000

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  Q3
1           T    T   F
2           F    F   F
3           T    T   T
The query below takes the columnar data and creates a row for each quarter in a temporary table:
CREATE PROCEDURE CalcGrades 
AS

SET NOCOUNT ON

DECLARE @StudentID int, @Q1 char(1), @Q2 char(1), @Q3 char(1)
Create a cursor from the original table:
DECLARE c  CURSOR FOR 
SELECT *
FROM Grades
ORDER BY StudentID

CREATE TABLE #Grades (IndexCol int identity (0,1), StudentID int,
Quarter char(2), TrueFalse char(1)) OPEN c FETCH NEXT FROM c INTO @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 END SELECT StudentID, Trues=Count(CASE WHEN TrueFalse=
'T' THEN 1 END), Falses=Count(CASE WHEN TrueFalse= 'F' THEN 1 END) FROM #Grades GROUP BY StudentID ORDER BY StudentID CLOSE c DEALLOCATE c RETURN 0
Then you can do a select with the aggregate function COUNT(), which will produce this result:
StudentID   Trues       Falses      
----------- ----------- ----------- 
1           2           1
2           0           3
3           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
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date