Get the total for this current year

[henk3]

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”;

[DaveMaxwell]

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 
  FROM income 
 WHERE YEAR(date) = YEAR(NOW())
 GROUP BY year, month

[henk3]

Thank you! This works

[m_hutley]

Though it’s somewhat inefficient to select the year, if you’ve stated that the year must be this year… just saying.

[r937]

and that WHERE clause isn’t sargable

try this instead –

WHERE `date` >= CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY