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?
TK123 source
share