Is it possible to do this arithmetic directly in a MYSQL query, and not in PHP?

Question

I have a query like this:

$query = "SELECT * FROM `profit_by_campaign` WHERE campaign_name = 'myCoolCampaign' AND request_date = '2017-10-16'";

try {
    $stmt = $this->dbh->prepare($query);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
catch(PDOException $e) {
    die('Error: unable to query ' . $e->getMessage());
}

After extracting the results, I do some logic as follows:

$total_revenue = 0;
$total_spending = 0;

foreach($rows as $row) {
    $total_revenue += $row['revenue'];
    $total_spending += $row['spending'];
}

$total_profit = ($total_revenue - $total_spending);

echo $total_profit;

So, basically I'm trying to get the total profit from the campaign by subtracting it from its revenue. My question is, can this logic be done directly in the MYSQL query itself, and not in PHP?

Answer 1

Try this query:

SELECT SUM(revenue - spending) AS profit
FROM profit_by_campaign
WHERE
    campaign_name = 'myCoolCampaign' AND
    request_date = '2017-10-16';