Dear All,
I have a survey questions with 5 options. I want to know how many users selected the option 1, option2 and so on till option 5.
I have built this query and i think it's not working for me.
Result Table:
SELECT TOP 1000 [Emp_id]
,[QID]
,[AnswerId]
FROM ResultTable
My calculated query:
SELECT QuestionTable.QuestionDesc,
COUNT(ResultTable_Organization.AnswerId) AS TotalAnswered,
SUM(CASE WHEN [AnswerId] = 5 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),
COUNT(ResultTable_Organization.AnswerId)) AS 'Disagree',
SUM(CASE WHEN [AnswerId] = 4 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),
COUNT(ResultTable_Organization.AnswerId)) AS ' Strongly Disagree', SUM(CASE WHEN [AnswerId] = 3 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),
COUNT(ResultTable_Organization.AnswerId)) AS 'Agree', SUM(CASE WHEN [AnswerId] = 2 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),
COUNT(ResultTable_Organization.AnswerId)) AS 'Not Applicable', SUM(CASE WHEN [AnswerId] = 1 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),
COUNT(ResultTable_Organization.AnswerId)) AS 'Strongly Agree'
FROM ResultTable_Organization INNER JOIN
QuestionTable ON ResultTable_Organization.QID = QuestionTable.QID
WHERE (ResultTable_Organization.QID = 1)
GROUP BY QuestionTable.QuestionDesc
my table data
| Emp_id |
QID |
AnswerId |
| 1111 |
1 |
5 |
| 2222 |
1 |
5 |
| 3333 |
1 |
4 |
| 4444 |
1 |
5 |
| 5555 |
1 |
4 |
| 6666 |
1 |
5 |
Expected output:
If option 5 70% Strongly Agree
If option 4 5% Agree
If option 3 5% Neutral
If option 2 10% Disagree
If option 1 10% Strongly Disagree
Can you please let me know where i'm wrong.
Thanks
