Understanding SQL and Counting Multiple-Choice Results

As a technical blogger, it’s essential to explore various SQL techniques and provide in-depth explanations. In this article, we’ll delve into two different methods for counting the number of respondents who answered ‘A’, ‘B’, etc., in a multiple-choice questionnaire.

Introduction to SQL and JSON Data

Before we dive into the code examples, let’s briefly discuss SQL and JSON data.

SQL (Structured Query Language) is a programming language designed for managing relational databases. It provides various commands for creating, manipulating, and querying data.

JSON (JavaScript Object Notation) is a lightweight data interchange format that consists of key-value pairs and arrays. In this article, we’ll be working with JSON data, but it’s essential to note that the approach can also be applied to table data.

Method 1: Sum Case

The first method involves using the SUM function in combination with the CASE statement to count the number of respondents who answered each choice. Here’s an example code snippet:

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'
  ) 

This code snippet uses the OPENJSON function to parse the JSON data and extract the values of q1 and q2. The CASE statement is then used to count the number of respondents who answered each choice.

The results are as follows:

As you can see, this method only produces one set of totals per question.

Method 2: Pivot

The second method involves using the PIVOT function to count the number of respondents who answered each choice. Here’s an example code snippet:

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

This code snippet uses the OPENJSON function to parse the JSON data and extract the values of q1. The PIVOT function is then used to count the number of respondents who answered each choice.

The results are as follows:

As you can see, this method also only produces one set of totals per question.

Method 3: Unpivot and Aggregate

The third method involves using the CROSS APPLY function to unpivot the JSON data and then aggregating the results. Here’s an example code snippet:

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

This code snippet uses the OPENJSON function to parse the JSON data and extract the values of q1 and q2. The CROSS APPLY function is then used to unpivot the data, and the results are aggregated using the GROUP BY clause.

The final result is:

As you can see, this method produces a single result set with all the questions and their corresponding counts.

Conclusion

In conclusion, SQL provides various techniques for counting multiple-choice results. The SUM function combined with the CASE statement is one approach, while the PIVOT function offers another way to achieve the same goal. However, when working with JSON data, it’s essential to use the OPENJSON function to parse the data correctly.

The third method involving unpivoting and aggregation using the CROSS APPLY function provides a flexible solution for this problem, allowing you to count all questions in a single statement.

References

• SQL Fiddle
• Microsoft Docs: OPENJSON
• Microsoft Docs: PIVOT

Last modified on 2024-02-12