i want to get month (last 12 month) + current month in mysql

hmm, ask the quy who told you "to use this query". Because this is not possible to do with it. Cause STR_TO_DATE(DC_Date,'%m') returns nothing. You need at least something like  STR_TO_DATE(DT, '%Y-%m-%d'). The correct format depends on your data.

CREATE TABLE Test ( DT VARCHAR(255) );

INSERT INTO Test VALUES ( '2017-07-27' );

SELECT STR_TO_DATE(DT, '%Y-%m-%d')
FROM Test;

Select allOpen in new window

See this fiddle.

Where's the GROUP BY?

Have a look at this SQLFiddle: http://sqlfiddle.com/#!9/3f1ce/4


It shows how easy it is to get what you want with DC_DATE stored in a date type column and not as 'String'.

Now you have to apply the conversion from your string to date via STR_TO_DATE(DC_DATE, '%Y-%m-%d') as ste5an said, using the format specific order of year, month and day. This just has to be applied in all places (I count 12 places) my code simply uses DC_DATE as I configured the fiddle to create a Test table with a date column. I don't do this, as it renders the query unreadable.

OK, now a little explanation: I assume by saying past 12 month and current month you explicitly wanted to mean the twelve last full months and the current month as far as it passed. Therefore I compute first day of current month via DATE_FORMAT(NOW() ,'%Y-%m-01') and go back 12 months from there via DATE_ADD(DATE_FORMAT(NOW() ,'%Y-%m-01'), INTERVAL -12 MONTH), that makes it 13 rows overall in any case, even if NOW() is quite near the end of a month, you get 13 rows result.

And the second query is a bonus showing you how to calculate a cumulative total.

Notice: Besides optimising your DC_DATE column to be a date in the Test data I also simplified to just have one column Amount instead of your two, this is just a matter of putting in your original names and expressions., the query logic remains the same.

And just a short rant: Don't ever store dates into string fields. String dates in date fields and querying them you get them in human readable format, this is already been thought of from not only database developers, but also any language developers. You can configure in what format they are displayed. But storing them the way you like them instead of learning how to configure their format kills the possibility to act on your data performant and let the SQL engine calculate with the dates, the conversion is not just a slight annoyance, it means every row has to processed in full table scan before being able to see whether a date in it belongs into the time span of the last 12-13 months or not. With data in the correct type and indexes MySQL (any SQL dialect of any database) would be able to pick out the rows of last year, even if the table collected data from 100 years already.

You partly make up for that problem by having data of just one year in your past_year_data table. If nomen est omen here, if the promise of the name is reality, you will not even need a where condition on the time span.