How to calculate percentages in mySQL

Question

I want to calculate a percentage for the number of successful applications grouped by State.

I'm using mySQL and I can run a query to obtain a list of states with the number of total applications and successful applications. I am not sure how to combine the two queries and then try and calculate a percentage value. I'm assuming I have to run a subquery of some kind here, but not too sure.

SELECT w.state, c.case_status, COUNT(w.state) AS No_Applications
FROM worksites w JOIN cases c USING (WorksiteID)
WHERE c.Case_Status like '%certified%'
GROUP BY w.state
ORDER BY w.state ASC;

SELECT w.state, COUNT(w.state) AS No_Total_Applications
FROM worksites w JOIN cases c USING (WorksiteID)
GROUP BY w.state
ORDER BY w.state ASC;

SELECT w.state, c.case_status, COUNT(w.state FROM worksites w JOIN cases c WHERE c.Case_Status like '%certified%') AS Success_Applications,
COUNT (w.state) AS Total_Applications
FROM worksites w JOIN cases c USING (WorksiteID)
GROUP BY w.state
ORDER BY No_Applications ASC;

The first two codes return the number of successful and total applications respectively; the third code was my attempt and merging the two -- it failed saying "Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM worksites w JOIN cases c WHERE c.Case_Status like '%certified%') AS Success' at line 1"

Probably a good idea to learn how to use `GROUP BY`. – Eric Jul 24 '19 at 23:22 — Eric, Jul 24 '19 at 23:22

Patrick · Answer 1 · 2019-07-25T09:22:26.087

0

You don't need to join 2 queries together, you can just extend your query so that it count number of certified without filtering the whole table

SELECT 
  w.state, 
  c.case_status, 
  COUNT(w.state) AS No_Total_Applications,
  SUM(CASE WHEN c.Case_Status like '%certified%' THEN 1 ELSE 0 END) AS No_Applications,
  SUM(CASE WHEN c.Case_Status like '%certified%' THEN 1 ELSE 0 END) / COUNT(w.state) AS Percentage
FROM worksites w JOIN cases c USING (WorksiteID)
GROUP BY w.state
ORDER BY w.state ASC;

edited Jul 25 '19 at 09:22

answered Jul 24 '19 at 22:01

Patrick

234
1
7

Got the following error: "Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS No_Applications, SUM(CASE WHEN c.Case_Status like '%certified%' THEN 1 EL' at line 4" – Shreyas Chaturvedi Jul 24 '19 at 22:06
Hello. Sorry forgotten the "END" clause at the end. Added above. – Patrick Jul 25 '19 at 09:22

score 0 · Accepted Answer · answered Jul 24 '19 at 23:19

In MySQL, you can simply do:

SELECT w.state, 
       SUM(c.Case_Status like '%certified%') AS No_Applications
       AVG(c.Case_Status like '%certified%') as ratio
FROM worksites w JOIN
     cases c
     USING (WorksiteID)
GROUP BY w.state
ORDER BY w.state ASC;

MySQL treats booleans as numbers in a numeric context, simplifying the arithmetic using them.

Also note that I removed Case_Status from the SELECT. It is not part of the GROUP BY, so it is not really appropriate (and there will be multiple values for each w.state).

How to calculate percentages in mySQL

2 Answers2