CODEWITHSUNDEEP
mysqlperformance
coder
coder

Reputation: 93

MySQL query to get last 12 month sales where month and year are different fields

I want to display last 12 months sales in a chart. SQL table has year and month field and not a combined date field. Im not able to give the interval condition of 12months on Year field.

SELECT s_month,s_year,SUM(s_amount) FROM table 
WHERE s_month >= Date_add(now(),interval - 12 month) 
AND s_year >= Date_add(now(),interval - 12 month)
GROUP BY s_year,s_month

Upvotes: 2

Views: 2561

Answers (2)

Rick James
Rick James

Reputation: 142278

This is a strong argument against storing date parts (month, year) in separate columns.

The WHERE clause you have does not do what you expect!

It is virtually always better to have a DATE column (or TIMESTAMP or DATETIME) and use date functions as needed to split it apart.

SELECT  MONTH(dat), YEAR(dat), SUM(amount)
    FROM  table
    WHERE  dat >= CURDATE() - INTERVAL 12 MONTH
    GROUP BY  LEFT(dat, 7) -- eg, "2017-12"

There is another problem with your query. SUM(amount) will have a partial month at either end. I can't solve that for you without better understanding where the data comes from and when. If it is already a single reading stored on the first of the month, then no problem. If it is daily or hourly amounts, then my point stands.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

One method is:

select s_year, s_Month, sum(s_amount)
from t
where date(concat_ws('-', s_year, s.month, 1)) >= curdate() - interval 12 month
group by s_year, s_month;

You may want to adjust the date arithmetic, depending on whether you want the date from 12 months ago.

If you want the last 12 months in the data, you can do:

select s_year, s_month, sum(amount)
from t
group by s_year, s_month
order by s_year desc, s_month desc
limit 12;

Upvotes: 2

Related Questions