Post reply

Perform 2 Counts in one query

  • 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

  • October 19, 2006 at 8:09 am

    #666806

    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...

  • October 19, 2006 at 8:12 am

    #666808

    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

  • October 19, 2006 at 8:25 am

    #666810

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

  • October 19, 2006 at 8:45 am

    #666820

    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/

  • October 19, 2006 at 8:46 am

    #666821

    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

  • October 19, 2006 at 8:54 am

    #666823

    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.

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

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