Quote:
Originally Posted by jaxkookie 
I have been trying to count each answer for each question and roll this up to one line item per question with totals for each answer. what I am getting is staggered answers. example, question 1 has 9 possible answers. I get the count for each, but question 1 is repeated 9 times. here is the part of the code I get the stagger results with:
CASE WHEN ANSWER_BANK.ANSWER_SEQUENCE < 1 THEN COUNT(ANSWER_BANK.ANSWER_SEQUENCE) END AS NO_ANS,
CASE WHEN ANSWER_BANK.ANSWER_SEQUENCE = '1' THEN COUNT(ANSWER_BANK.ANSWER_SEQUENCE) END AS ANS1_COUNT,
CASE WHEN ANSWER_BANK.ANSWER_SEQUENCE = '1' THEN V_ASSESSMENTREPORT.ANSWER_TEXT END AS ANS1_TEXT,
CASE WHEN ANSWER_BANK.ANSWER_SEQUENCE = '2' THEN COUNT(ANSWER_BANK.ANSWER_SEQUENCE) END AS ANS2_COUNT,
CASE WHEN ANSWER_BANK.ANSWER_SEQUENCE = '3' THEN COUNT(ANSWER_BANK.ANSWER_SEQUENCE) END AS ANS3_COUNT,
CASE WHEN ANSWER_BANK.ANSWER_SEQUENCE = '4' THEN COUNT(ANSWER_BANK.ANSWER_SEQUENCE) END AS ANS4_COUNT,
CASE WHEN ANSWER_BANK.ANSWER_SEQUENCE = '5' THEN COUNT(ANSWER_BANK.ANSWER_SEQUENCE) END AS ANS5_COUNT,
CASE WHEN ANSWER_BANK.ANSWER_SEQUENCE = '6' THEN COUNT(ANSWER_BANK.ANSWER_SEQUENCE) END AS ANS6_COUNT,
CASE WHEN ANSWER_BANK.ANSWER_SEQUENCE = '7' THEN COUNT(ANSWER_BANK.ANSWER_SEQUENCE) END AS ANS7_COUNT,
CASE WHEN ANSWER_BANK.ANSWER_SEQUENCE = '8' THEN COUNT(ANSWER_BANK.ANSWER_SEQUENCE) END AS ANS8_COUNT,
CASE WHEN ANSWER_BANK.ANSWER_SEQUENCE = '9' THEN COUNT(ANSWER_BANK.ANSWER_SEQUENCE) END AS ANS9_COUNT
I just want one line item per question and a total count for each answer. I also need to know how to count the blank responses.
Hi
Please post your database table structure, sample data for the same table and the whole query with you are trying to get the result. Also provide how the result u want against the data u'll post. The contents you posted are not sufficient to provide any kind of help to you.
Also use code tags in your future posts thats make easy to read the code :)
Kind Regds,
Bhushan
