I have a number of responses for a multiple-choice questionnaire. My goal is to count the number of respondents who answered 'A', 'B' etc and further process that data.
(The raw data is in JSON but this could be table data too. The JSON format isn't really a factor but if there's a better way of fetching the results form JSON then I'm open to ideas).
I have two methods of counting the responses, but my approach only produces one set of totals per question.
Method 1: Sum Case
DECLARE @json NVARCHAR(MAX);
SET @json = N'[
{"q1": "B", "q2": "B"},
{"q1": "C", "q2": "C"},
{"q1": "D", "q2": "C"},
{"q1": "A", "q2": "B"},
{"q1": "A", "q2": "B"},
{"q1": "C", "q2": "A"},
{"q1": "C", "q2": "B"}
]';
SELECT
Count(*) as total,
sum(case when q1 = 'A' then 1 else 0 end) as A,
sum(case when q1 = 'B' then 1 else 0 end) as B,
sum(case when q1 = 'C' then 1 else 0 end) as C,
sum(case when q1 = 'D' then 1 else 0 end) as D
FROM OPENJSON(@json)
WITH (
q1 NVARCHAR(50) '$.q1',
q2 NVARCHAR(50) '$.q2'
)
Results:
total | A | B | C | D |
---|---|---|---|---|
7 | 2 | 1 | 3 | 1 |
Method 2: Pivot
DECLARE @json NVARCHAR(MAX);
SET @json = N'[
{"q1": "B", "q2": "B"},
{"q1": "C", "q2": "C"},
{"q1": "D", "q2": "C"},
{"q1": "A", "q2": "B"},
{"q1": "A", "q2": "B"},
{"q1": "C", "q2": "A"},
{"q1": "C", "q2": "B"}
]';
SELECT *
FROM OPENJSON(@json)
WITH (
q1 NVARCHAR(50) '$.q1'
) t
PIVOT(
COUNT(q1)
FOR q1 IN
(
[A],
[B],
[C],
[D]
)
) as pivot_table
Results:
A | B | C | D |
---|---|---|---|
2 | 1 | 3 | 1 |
Desired outcome
Is there a way in which I can count all the questions with a single statement?
Question | A | B | C | D |
---|---|---|---|---|
q1 | 2 | 1 | 3 | 1 |
q2 | 1 | 3 | 2 | 0 |
CodePudding user response:
I think the WITH
might get q1,q2
instead of q1,q1
Because q1
and q2
are two-columns we can try to use CROSS APPLY
value to make unpivot, then use the aggregate condition function.
SELECT
v.Question,
sum(case when v.val = 'A' then 1 else 0 end) as A,
sum(case when v.val = 'B' then 1 else 0 end) as B,
sum(case when v.val = 'C' then 1 else 0 end) as C,
sum(case when v.val = 'D' then 1 else 0 end) as D
FROM OPENJSON(@json)
WITH (
q1 NVARCHAR(50) '$.q1',
q2 NVARCHAR(50) '$.q2'
)
CROSS APPLY (VALUES ('q1',q1),('q2',q2)) v(Question,val)
GROUP BY v.Question