T-SQL to perform 2 COUNTs in 1 Pass (if possible)
-
Looking for T-SQL to accomplish 2 "COUNTS" in 1 pass (if possible). I need the:
1. COUNT of total questions
2. COUNT of 'Correct' Questions (see --NOTE below)
Grouped by the ExamKnowledgeAreaID within the ExamID.
INSERT INTO target_tbl
(T1,
T2,
TotalQuestions,
TotalCorrect)
SELECT
A.ExamID,
B.ExamKnowledgeAreaID,
COUNT(A.QuestionCode), -- COUNT of "ALL" QUESTIONS
COUNT(A.Correct) -- COUNT of "Correct" Questions
FROM tbl_a A INNER JOIN tbl_b B ON A.QuestionCode = B.QuestionID
GROUP BY ER.ExamID, Q.ExamDomainKnowledgeAreaID, ???
WHERE ???
-- NOTE: the column A.CORRECT contains either of 2 values: -1 = Correct, 0 = InCorrect
Here is conceptual Sample Report Output:
Exam Exam Knowledge Total Total
ID Area Questions Correct
------ ---- --------- -------
10 T-SQL Knowledge 10 9
10 Cluster Knowledge 15 14
25 Windows XP O/S 10 8
25 Windows XP Misc 20 17
thx in advance
BT -
Couldn't you do a sum(abs(a.correct)), instead of a count, to give you the total of correct questions?
And then again, I might be wrong ...
David Webb -
You should Follow the above approach but swap the order of operations:
abs(sum(a.correct))
-- abs performed once
* Noel -
create table #tbl_A (pk int identity, Exam_id int, Question_Code int, Correct smallint)
create table #tbl_B (Question_id int identity, Exam_Knowledge_Area varchar(100), Question varchar(1000))
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('T-SQL Knowledge', 'Question 1')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('T-SQL Knowledge', 'Question 2')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('T-SQL Knowledge', 'Question 3')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('T-SQL Knowledge', 'Question 4')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('Cluster Knowledge', 'Question 1')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('Cluster Knowledge', 'Question 2')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('Cluster Knowledge', 'Question 3')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('Cluster Knowledge', 'Question 4')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('Windows XP O/S', 'Question 1')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('Windows XP O/S', 'Question 2')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('Windows XP O/S', 'Question 3')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('Windows XP O/S', 'Question 4')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('Windows XP Misc', 'Question 1')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('Windows XP Misc', 'Question 2')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('Windows XP Misc', 'Question 3')
insert into #tbl_B (Exam_Knowledge_Area, Question)
Values ('Windows XP Misc', 'Question 4')
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (1,1,0)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (1,2,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (1,3,0)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (1,4,0)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (1,5,0)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (1,6,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (1,7,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (1,8,0)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (1,9,0)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (1,10,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (1,11,0)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (1,12,0)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (2,1,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (2,2,0)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (2,3,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (2,4,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (2,5,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (2,6,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (2,7,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (2,8,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (2,13,0)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (2,14,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (2,15,-1)
insert into #tbl_A (Exam_id,Question_Code,Correct)
values (2,16,-1)
SELECT Exam_ID, Exam_Knowledge_Area, COUNT(A.Question_Code),
sum(abs(Correct))
FROM #tbl_A A
JOIN #tbl_B B ON A.Question_Code = b.Question_id
Group by Exam_ID, Exam_Knowledge_Area
Drop table #tbl_A
Drop table #tbl_B
-
THANKS! The SUM(ABS(CORRECT) worked fine. Much appreciated!
BT
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply