SQL: Counting the answers from a multiple-choice result-CodePudding

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

sqlfiddle