How to Calculate the Percentage of Employees Who Took a Survey in MySQL?

Calculating Percentage in MySQL

When working with a MySQL database, you may encounter the need to calculate percentages based on certain values. One such scenario is determining the percentage of members within a group who have taken a survey.

Problem Statement

Consider the following MySQL database table named "a_test" with four columns: "id", "group_name", "employees", and "surveys". The goal is to calculate the percentage of employees who have participated in the survey based on the number of surveys taken.

Provided SQL Statement

The following SQL statement has been attempted to solve this problem:

<code class="sql">SELECT
  group_name,
  employees,
  surveys,
  COUNT( surveys ) AS test1, 
  ((COUNT( * ) / ( SELECT COUNT( * ) FROM a_test)) * 100 ) AS percentage
FROM
  a_test
GROUP BY
  employees</code>

Revised Solution

To accurately calculate the percentage of employees who have taken the survey, the following revised SQL statement is provided:

<code class="sql">SELECT group_name, employees, surveys, COUNT( surveys ) AS test1, 
        concat(round(( surveys/employees * 100 ),2),'%') AS percentage
FROM a_test
GROUP BY employees</code>

Explanation

• round rounds the percentage to two decimal places.
• concat combines the percentage with the percentage symbol ('%').

This ensures that the result is displayed as a percentage with the correct number of decimal places.

Sample Data and Output

Using the following sample data in the "a_test" table:

<code class="sql">INSERT INTO a_test (id, group_name, employees, surveys) VALUES
(1, 'Awesome Group A', '100', '0'),
(2, 'Awesome Group B', '200', '190'),
(3, 'Awesome Group C', '300', '290');</code>

The revised SQL statement produces the following output:

group_name	employees	surveys	percentage
Awesome Group A	100	0	0.00%
Awesome Group B	200	190	95.00%
Awesome Group C	300	290	96.67%

As evident from the output, the percentage of employees who have taken the survey is calculated correctly. Awesome Group B has 95% participation, while Awesome Group C has 96.67% participation.

The above is the detailed content of How to Calculate the Percentage of Employees Who Took a Survey in MySQL?. For more information, please follow other related articles on the PHP Chinese website!