Perform 2 Counts in one query

Question

Perform 2 Counts in one query

Trystan Hughes

SSCrazy

Points: 2109
More actions
October 19, 2006 at 7:43 am

#174970

Hi all,
I want to be able to perform two counts in the same query so that I can compare the values. I have a table that contains questions, and a table that contains answers. The Answers table contains all the answers to the questions where they contain a QuestionUID so that they point the question is which they relate to. Answers that relate to the same question are distinguished by an InspectionUID. So, the user starts a new Inspection and is presented with a set of questions in which they give Answers against. There can only be one answer to each Question for a given Inspection. Some of the Questions are required.
My problem is that I want the first count in the query to be a count of all the required Questions for a Inspection, and then a count on the Answers against these Required Questions so that I know a given Inspection is deemed complete.
I have the following query, which seems to be bringing back nothing, although I have 3 required questions in the Questions Table. I have no Answers in the Answer table, but NULL is getting returned, not 0.
SELECT COUNT(*) AS MainCount,
SUM(CASE WHEN A.InspectionUID LIKE '00052_0000010890' THEN 1 ELSE 0 END) AS SubCount
FROM tbl_NSP_QuestionTree AS Q LEFT OUTER JOIN
tbl_NSP_Answer AS A ON (Q.QuestionTreeUID = A.QuestionTreeUID)
WHERE (Q.ParentUID = 2)
AND (Q.IsRequired = 1)
AND (A.InspectionUID LIKE '00052_0000010890')
I hope you understand what I am trying to achieve with this, if not them please ask me to expand on this further.
(once again I am using SQL Mobile)
Thanks
Tryst

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

dlineberry SSC Journeyman Points: 91 More actions · Answer 1

I might be off base here but this is what it looks like to me; however, this will give you multiple QuestionTreeUID's and Question counts that are the same but will list a count of how many answers there are per question.

select QuestionTreeUID, count(*) as QCount, (select count(*) from tbl_NSP_Answer where InspectionUID like '%00052_0000010890%' and QuestionTreeUID = Q.QuestionTreeUID) as ACount

from tbl_NSP_QuestionTree Q

where ParentUID = 2

and IsRequired = 1

group by QuestionTreeUID

I hope this helps...

CK Bhatia Say Hey Kid Points: 679 More actions · Answer 2

If I have understood your issue correctly - then the following should work

SELECT COUNT(*) AS MainCount,

isnull((select count(*) from tbl_NSP_Answer AS A

where Q.QuestionTreeUID = A.QuestionTreeUID

and A.InspectionUID LIKE '00052_0000010890%'

&nbsp, 0) as SubCount

FROM tbl_NSP_QuestionTree AS Q

WHERE (Q.ParentUID = 2)

AND (Q.IsRequired = 1)

I feel the need - the need for speed

CK Bhatia

dlineberry SSC Journeyman Points: 91 More actions · Answer 3

Looks like the same query but good call on the isnull protecting against if there are no answers for a particular question.

Vladan SSC-Insane Points: 22072 More actions · Answer 4

I hope it will be easier than that - although I may have misunderstood something... Try this:

SELECT A.InspectionUID, COUNT(*) as cnt_required, COUNT(A.QuestionTreeUID) as cnt_req_answered

FROM tbl_NSP_QuestionTree Q

LEFT OUTER JOIN tbl_NSP_Answer A ON (Q.QuestionTreeUID = A.QuestionTreeUID)

WHERE (Q.ParentUID = 2)AND (Q.IsRequired = 1)

GROUP BY A.InspectionUID

I don't know what Q.ParentUID is - but I suppose that it is identifiacation of the set of questions.

Instead of limiting the query to one inspection, I wrote it to return all inspections for the given Parent. If both counts are equal, it means that all required questions were answered. Query takes advantage of the fact that COUNT(column_name) eliminates rows with NULL in column_name.

If I didn't answer your question or if it does not work as supposed, please explain the problem in more detail.

/edit : sorry, forgot the GROUP BY... added now/

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 5

Thanks for the replies people.

SQL Mobile only allows Sub Queryies that reside in the IN clause, the 2nd query here will not be valid.

I will try the first one, but are you saying null will be returned instead of 0 if no answers exists for the questions of a specified Inspection?

Thanks

Tryst

Vladan SSC-Insane Points: 22072 More actions · Answer 6

We posted almost in the same moment

Anyway, my query will return 0, not NULL - no matter whether any questions were answered or not in the inspection.