in a single mysqli query get two counts and perform a calculation

Question

I am wanting to:

count how many rows that have the column date = $date

second count hows many rows the column date = $date AND column result = word 'win'

then divide the first count value by the second and echo the result/value.

The value is returing as 1 - when the number of rows = 3 , those with Win in result column = 2 , the result should be 1.5.

When I even do echo "".$testrow['win_count'] . ""; the result is still 3, which is wrong thats the result of total number of rows.

  <?php

    ini_set('display_errors', 1); error_reporting(E_ALL ^ E_NOTICE);

    $connection = mysqli_connect("*****", "*****", "*****", "*****");
    if (!$connection) {
        die("Database connection failed: " . mysqli_connect_error());
    }

    $Date = '2019-03-11';
    $Win = 'Win';

$testsql="
SELECT 
count(*) AS bet_count,
count(IF(result ='$Win', 1, 0)) AS win_count
FROM bets WHERE betDate = '$Date' GROUP BY betDate 
";
    $testresult = mysqli_query($connection, $testsql);

while ($testrow = mysqli_fetch_assoc($testresult))
{ 
    echo "<tr>";
echo "<td class='text-center'>".($testrow['bet_count']/$testrow['win_count']). "</td>";
echo "</tr>";
}

mysqli_close($connection);
?>

[How to enable MySQLi exception mode?](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) — Dharman, Mar 08 '19 at 20:30
@Dharman - the query error is mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in line 467 , line 467 is while ($testrow = mysqli_fetch_assoc($testresult)) — George Richardson, Mar 08 '19 at 20:34
That is not the query error, this is PHP error. You have no idea whether the query was successful or not. — Dharman, Mar 08 '19 at 20:48

score 0 · Answer 1 · answered Mar 08 '19 at 21:39

This....

count(IF(result = '$Win', 1, 0)) AS win_count

should be....

count(IF(result = '$Win', 1, NULL)) AS win_count

or...

count(CASE WHEN result = '$Win' THEN 1 END)) AS win_count

Also I recommend you do some reading on MySQL COUNT() AND SUM(), so you understand why I used NULL instead of 0 in my IF() contained in the COUNT() and why I simply removed the ELSE NULL from my example CASE statement in the COUNT()....

in a single mysqli query get two counts and perform a calculation

1 Answers1