Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Percentages in MySQL - Between two columns in the same table

I have a MySQL Table that looks like this:

Name    | Pass |  Fail | Pass Percent | Fail Percent

Abdy    | 20   |  5    |              |
Bob     | 10   |  5    |              |
Cantona | 40   |  10   |              |
Dave    | 30   |  20   |              |

I am trying to get the percentages:

like : passpercent = (pass/pass+fail)*100

Can I fill the table with a single MySQL code for both the columns??

The Table would look like this hopefully:

Name    | Pass |  Fail | Pass Percent | Fail Percent

Abdy    | 20   |  5    |   80         |  20
Bob     | 10   |  5    |   66         |  33
Cantona | 40   |  10   |   80         |  20
Dave    | 30   |  20   |   60         |  40
like image 663
Kumaran Senapathy Avatar asked May 13 '13 18:05

Kumaran Senapathy


1 Answers

That's absolutely possible.

To fill the second table:

UPDATE mytable SET pass_pct=(pass/pass+fail)*100,fail_pct=(fail/pass+fail)*100

Granted, you could also generate those during selection of the first table (if you don't want to store the results), like:

SELECT name,pass,fail,(pass/pass+fail)*100 as pass_pct,(fail/pass+fail)*100 as fail_pct FROM mytable
like image 65
dougBTV Avatar answered Sep 26 '22 17:09

dougBTV