How to Calculate Employee Survey Participation Percentage in MySQL with Subqueries?

Calculating Employee Survey Participation Percentage in MySQL

In a scenario where a database contains information about employees and their survey participation, calculating the percentage of employees who have completed surveys is essential for assessing response rates. To achieve this in MySQL, a carefully crafted query is required.

The provided query attempts to calculate the percentage using division and multiplication operations. However, it falls short due to the calculation being based on the number of rows in the table instead of the employees represented in the 'employees' column.

The corrected query introduces the following adjustments:

• The percentage calculation is now a combination of two subqueries:

  • test1 counts the number of surveys taken by each employee.
  • The outer subquery calculates the total number of employees.
• The percentage is then calculated by dividing test1 by the total number of employees and multiplying the result by 100.

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

This updated query accurately calculates the percentage of employees who have participated in surveys, as shown in the demo linked in the answer. For example, Awesome Group A would have a percentage of 0%, while Awesome Group B would have a percentage of 95%.

The above is the detailed content of How to Calculate Employee Survey Participation Percentage in MySQL with Subqueries?. For more information, please follow other related articles on the PHP Chinese website!