Taylor Made PT Taylor Made PT - 6 months ago 11
SQL Question

How to have a sum() with a where clause

I currently have:



// connect for other mins
$sql = "SELECT sum(mins) FROM completed_activity WHERE member_id = '$_SESSION[SESS_MEMBER_ID]' AND exercise != 'Personal Training' AND date >= '$mon'";





But, when the date is not within the where clause, nothing is shown. I'm not even getting my 'else' statement.

Any ideas?

MAYBE SOLVED
I may have got the correct way of doing it with:



$sql = "SELECT sum(CASE WHEN member_id = '$_SESSION[SESS_MEMBER_ID]' AND exercise != 'Personal Training' AND date >= '$mon' THEN mins ELSE 0 END) AS mins FROM completed_activity";





Can anyone confirm if this looks right?

Answer

When using SUM, there are at least one result returned. The result of SUM may be 0 or more.

That's why your code is not working correctly.

Try the following code:

// connect for other mins
$sql = "SELECT sum(mins) AS mins_sum FROM completed_activity WHERE member_id = '$_SESSION[SESS_MEMBER_ID]' AND exercise != 'Personal Training' AND date >= '$mon'";

$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
    $mins = $row["mins_sum"];
    if ($mins > 0) {
        echo "yes";
    } else {
        echo "no";
    }
}