如何计算表中的多个列并使用php显示它们

Question

I have a table with some columns (see the pic below):

My Table:

I'd like to count values in that columns (how many "ok" and "no" I have in column over05, over15, etc etc) using some filters (i.e. best_bets between 1.20 and 1.50) and show them using php

Now I can do a count just for a column using code below:

    $result=mysql_query("SELECT count(over15) as total from risultati WHERE over15 = 'OK' AND best_bets >= 1.15 AND best_bets <= 1.50");
$data=mysql_fetch_assoc($result);
echo $data['total'];

but I'd like to do it for more columns

Regards

EDIT:I tried in this way, but it's not working:

$result=mysql_query("SELECT SUM(CASE WHEN over05 = 'OK' THEN 1 ELSE 0 END) AS OK_05,
       SUM(CASE WHEN over15 = 'OK' THEN 1 ELSE 0 END) AS OK_15,
       SUM(CASE WHEN over25 = 'OK' THEN 1 ELSE 0 END) AS OK_25,
       SUM(CASE WHEN over35 = 'OK' THEN 1 ELSE 0 END) AS OK_35,
       SUM(CASE WHEN over45 = 'OK' THEN 1 ELSE 0 END) AS OK_45
FROM risultati
WHERE best_bets BETWEEN 1.15 AND 1.5O");
$data=mysql_fetch_assoc($result);
echo $data['OK_15'];
echo $data['OK_25'];
echo $data['OK_35'];
echo $data['OK_45'];

edit2: I also tried in another way, but nothing:

$result=mysql_query("SELECT SUM(CASE WHEN over05 = 'OK' THEN 1 ELSE 0 END) AS OK_05,
       SUM(CASE WHEN over15 = 'OK' THEN 1 ELSE 0 END) AS OK_15,
       SUM(CASE WHEN over25 = 'OK' THEN 1 ELSE 0 END) AS OK_25,
       SUM(CASE WHEN over35 = 'OK' THEN 1 ELSE 0 END) AS OK_35,
       SUM(CASE WHEN over45 = 'OK' THEN 1 ELSE 0 END) AS OK_45
FROM risultati
WHERE best_bets BETWEEN 1.15 AND 1.5O");



while ($row = mysql_fetch_assoc($result)) 
{
    echo $row['OK_05'];
    echo $row['OK_15'];
    echo $row['OK_25'];
    echo $row['OK_35'];
    echo $row['OK_45'];

}

Answer 1

Use condtional aggregation:

SELECT SUM(CASE WHEN over05 = 'OK' THEN 1 ELSE 0 END) AS OK_05,
       SUM(CASE WHEN over15 = 'OK' THEN 1 ELSE 0 END) AS OK_15,
       SUM(CASE WHEN over25 = 'OK' THEN 1 ELSE 0 END) AS OK_25,
       SUM(CASE WHEN over35 = 'OK' THEN 1 ELSE 0 END) AS OK_35,
       SUM(CASE WHEN over45 = 'OK' THEN 1 ELSE 0 END) AS OK_45
FROM risultati
WHERE best_bets BETWEEN 1.15 AND 1.5O    -- or whatever restrictions you want

This approach allows you to gather stats for all your age columns in a single query, whereas putting the column check for 'OK' limits to a single column in one query.