[RAILS] 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)

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

• PostgreSQL 9.6

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

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

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