使用PHP计算ENUM类型列的百分比

Question

In my SQL database I have one table with some items with a column that are ENUM type and has 3 options: 'pending', 'discount', 'received'

How can I print the population percentage of 'received' option, like (In my table I have 40% of 'received' option that are selected) ?

Thank you!

Answer 1

To get the totals for each is pretty simple (assuming your enum column is called 'status' and has a column called id):

SELECT status, count(id) FROM myTable GROUP BY status;

While it would be possible to write your query to calculate the actual percentages, the simplest thing from there is to do the math yourself

If you choose to use mysqli to run your query, the code would be something like this:

// create mysqli connection
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$res = $mysqli->query("SELECT status, count(id) AS subtotal FROM myTable GROUP BY status");
$totals = array();
while ($row = $res->fetch_assoc()) {
    $totals[$row['status']] = $row['subtotal'];
}

This will give you an array like ['pending' => 100, 'discount' => 200, 'received' => 50]):

$total = $result['pending'] + $result['discount'] + $result['received'];
$pctPending = ($result['pending']*100)/$total;

ans so forth. You will of course have to adjust the code to match how your results come back, but the math applies regardless.