I have the following SQL statement that works to get the total for the last 12 months.
How do I modify this to give me the total of THIS year only…and not go back 12 months. The statement creates a bar chart for me and I am only interested in this years results.
$sql = “SELECT YEAR(date) AS year, MONTH(date) AS month, SUM(amount) AS total FROM income WHERE date > DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY year, month”;
You mean something like this, which will return just January of 2020 as of today?
SELECT YEAR(date) AS year
, MONTH(date) AS month
, SUM(amount) AS total
WHERE YEAR(date) = YEAR(NOW())
GROUP BY year, month
Though it’s somewhat inefficient to select the year, if you’ve stated that the year must be this year… just saying.
and that WHERE clause isn’t sargable
try this instead –
WHERE `date` >= CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.