Calculate the percentage of "good", "normal", and "bad" in the questionnaire using SQL

Thing you want to do

I would like to find the percentage of "good", "normal", and "bad" included in the surveys. What kind of SQL should I write?

Data example

Contents of surveys table (13 items in total)

id user_id answer
1 251 Was good
2 113 Was good
3 46 Usually
4 414 Was good
5 456 Was good
6 18 Usually
7 173 Usually
8 441 Was good
9 419 It was bad
10 157 Usually
11 116 Was good
12 204 Was good
13 445 It was bad

Value you want to calculate

--Good = 53.8% (because there are 7) --Usually = 30.8% (because there are 4 cases) --It was bad = 15.4% (because there are 2 cases)

Target RDBMS

Answer example

It can be calculated with such SQL.

SELECT
  TO_CHAR(
    100.0 * SUM(CASE WHEN answer = 'Was good' THEN 1 ELSE 0 END) / COUNT(*),
    '999.9%'
  ) AS "Was good",
  TO_CHAR(
    100.0 * SUM(CASE WHEN answer = 'Usually'   THEN 1 ELSE 0 END) / COUNT(*),
    '999.9%'
  ) AS "Usually",
  TO_CHAR(
    100.0 * SUM(CASE WHEN answer = 'It was bad' THEN 1 ELSE 0 END) / COUNT(*),
    '999.9%'
  ) AS "It was bad"
FROM surveys

Output result

Was good Usually It was bad
1 53.8% 30.8% 15.4%

Brief commentary

--SUM (CASE WHEN answer ='Good' THEN 1 ELSE 0 END) counts how many" good "were. (7 here) --Count the total number of items in the table with COUNT (*) (13 here) --You can calculate the percentage by dividing by the above two numbers and multiplying by 100. --However, since the division between integers is rounded to an integer value, 7 ÷ 13 × 100 = 0. To avoid that, multiply by 100.0 first instead of 100. As a result, the value after the decimal point is also calculated. (100.0 × 7 ÷ 13 = 53.8461538) --Finally, format the number with the TO_CHAR function (TO_CHAR (53.8461538, '999.9%') 53.8%) --Calculate the percentage of "normal" and "bad" using the same procedure

Actually execute

You can check the SQL execution result by accessing the following site and clicking the "Run it" button.

https://rextester.com/XINWIX39774 Screen Shot 2020-10-23 at 10.01.33.png

Bonus: Implementation example in Rails

If you implement it with Ruby on Rails, it looks like this.

#Write SQL in SELECT clause
sql = <<~SQL
100.0 * SUM(CASE WHEN answer = 'Was good' THEN 1 ELSE 0 END) / COUNT(*) AS good,
100.0 * SUM(CASE WHEN answer = 'Usually'   THEN 1 ELSE 0 END) / COUNT(*) AS fair,
100.0 * SUM(CASE WHEN answer = 'It was bad' THEN 1 ELSE 0 END) / COUNT(*) AS bad
SQL

#Execute SQL to get the value
survey = Survey.select(sql)[0]
survey.good #=> 53.8461538
survey.fair #=> 30.7692308
survey.bad  #=> 15.3846154

Supplementary explanation

--If you change the column alias to Japanese, it will not be compatible with the program, so here it is good / fair / bad. --The value format is View's responsibility, so here we just return the calculated value purely. --If you set Survey.select (sql) .first, the ORDER BY clause will be added automatically and an SQL error will occur, so I dared to set it to[0].

Another solution

From the comment section of this article.

Use AVG function

SELECT
  TO_CHAR(
    AVG(CASE WHEN answer = 'Was good' THEN 100 ELSE 0 END),
    '999.9%'
  ) AS "Was good",
  TO_CHAR(
    AVG(CASE WHEN answer = 'Usually'   THEN 100 ELSE 0 END),
    '999.9%'
  ) AS "Usually",
  TO_CHAR(
    AVG(CASE WHEN answer = 'It was bad' THEN 100 ELSE 0 END),
    '999.9%'
  ) AS "It was bad"
FROM surveys;

(The execution result is the same as the above text)

Average in the row direction, not in the column direction

SELECT
  answer,
  TO_CHAR(
    100.0 * COUNT(*) / (SELECT COUNT(*) FROM surveys),
    '999.9%'
  ) AS "rate"
FROM surveys
GROUP BY answer
ORDER BY rate DESC;

Execution result

answer rate
Was good 53.8%
Usually 30.8%
It was bad 15.4%

Recommended Posts

Calculate the percentage of "good", "normal", and "bad" in the questionnaire using SQL
[For beginners] DI ~ The basics of DI and DI in Spring ~
The comparison of enums is ==, and equals is good [Java]
[Ruby] Creating code using the concept of classes and instances
Create more Tabs and Fragments in the Fragment of BottomNavigationView
[Rails] Creation of the second vertical axis and Japanese localization in graph drawing using amCharts4, etc.
[Java] Get the dates of the past Monday and Sunday in order
Let's consider the meaning of "stream" and "collect" in Java's Stream API.
Coexistence of Flyway in the embedded database (h2) of the development environment and the release database (SQL Server) with Spring Boot