MySQL Calculate Percentage

Question

I have a MySQL database with 4 items: id (numerical), group_name, employees, and surveys.

In my SELECT I need to calculate the percentage of 'employees' who, by the number in 'surveys', have taken the survey.

This is the statement I have now:

SELECT
  group_name,
  employees,
  surveys,
  COUNT( surveys ) AS test1, 
  ((COUNT( * ) / ( SELECT COUNT( * ) FROM a_test)) * 100 ) AS percentage
FROM
  a_test
GROUP BY
  employees

Here is the table as it stands:

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

I would love to calculate the percentage of employees who by the number in surveys have taken the survey. i.e. as shown in the data above, the Awesome Group A would be 0% and Awesome Group B would be 95%.

This `GROUP BY` practice should not be used. It is non-standard and will produce undesired results. — Kermit, Apr 01 '13 at 15:55
Here is the table as it stands: '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');' I would love to calulate the percentage of 'employees' who by the number in 'surveys' have taken the survey. IE like above the Awesome Group A would be 0% and Awesome Group B would be 95% — user2232709, Apr 01 '13 at 16:01
Please note that you can edit the question to add new details. Code in comments is unreadable. — Álvaro González, Apr 01 '13 at 16:07
@Thomas [MySQL chooses which fields to `GROUP BY` as it feels](https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html). All other platforms require that non-aggregated fields in the `SELECT` appear in the `GROUP BY`. MySQL doesn't follow this standard. — Kermit, Apr 03 '15 at 23:48
@Thomas Just to add that it isn't the fields that it chooses as it feels, it's the value for the non-group fields which it chooses to display. In 5.8 (maybe 5.7 too) the behaviour is now consistent with other databases. The non-standard behaviour was useful but dangerous if you were relying on it. — Chris McCauley, Apr 18 '19 at 11:33

echo_Me · Accepted Answer · 2013-04-01T16:11:36.520

88

try this

   SELECT group_name, employees, surveys, COUNT( surveys ) AS test1, 
        concat(round(( surveys/employees * 100 ),2),'%') AS percentage
    FROM a_test
    GROUP BY employees

DEMO HERE

edited Apr 01 '13 at 16:11

answered Apr 01 '13 at 16:05

echo_Me

37,078
5
58
78

How can I use the column alias here? Like if I am calculating the employees value like "employees_type1+employees_type2 AS employees, then is it possible that I can use the employees alias there in the concat function ? – Happy Coder Aug 01 '14 at 09:54
No you cant. you have to use the join or subquery. – echo_Me Aug 14 '14 at 10:08

MySQL Calculate Percentage

1 Answers1

Linked

Related