2

This my Table Structure

        ###Table                        ###Expected Outpoot

    ID |    create_date                 Month | Application | Year
    ------------------------------      --------------------------
    1  |    2017-06-25 10:00:11         10   |      0       | 2016
    2  |    2017-06-26 10:00:11         11   |      0       | 2016
    3  |    2017-07-02 10:00:11         12   |      0       | 2016
    4  |    2017-07-25 10:00:11         1    |      0       | 2017
    5  |    2017-08-21 10:00:11         2    |      0       | 2017
    6  |    2017-08-22 10:00:11         3    |      0       | 2017
    7  |    2017-08-25 10:00:11         4    |      0       | 2017
                                        5    |      0       | 2017
                                        6    |      2       | 2017
                                        7    |      2       | 2017
                                        8    |      3       | 2017
                                        9    |      0       | 2017

I am Trying to get the monthly data count from my table. I want to start the count form Previous 3 month.

This is what I have tried so far?

Edit: I am close to solve the issue.Here is the query and result.

select date_format(tn.create_date,'%Y-%m') as mon,
count(*) as num
FROM table_name as tn
GROUP BY mon order by mon;


Month       |   Application 
--------------------------------
2017-06     |       2       
2017-07     |       2       
2017-08     |       3

So, How do i get my expected output?

Here is the Query Fiddle

Community
  • 1
  • 1
always-a-learner
  • 3,671
  • 10
  • 41
  • 81

1 Answers1

2

You need a list of months to join into your query. You can make a table for it, as @JohnHC suggested or use this hack to list the months like this:

set @start='2017-06-01';

select YEAR(date), MONTH(date), COUNT(create_date) from 
(
    select adddate(@start, INTERVAL @num:=@num+1 MONTH) date
    from test, (select @num:=-9) num
    limit 12
) as dt
LEFT JOIN test ON MONTH(create_date) = MONTH (date) and YEAR(create_date) = YEAR(date)
GROUP BY date;

Notice, that in the dt query, the actual data from the test table is not accessed, but it is required to contain at least 12 rows to work. Also, you don't need to use the same table for generating the month sequence what you use for querying the create_date.

You can set the start of the interval in the @num:=-9 expression. -9 in this case means 9 months before @start date. You can set the length of the interval in the LIMIT clause.

Example fiddle.

Laposhasú Acsa
  • 1,550
  • 17
  • 18
  • Why 12 rows required sir at the beginning may be no row available in table. then it would show all zero. – always-a-learner Sep 05 '17 at 09:18
  • It is because the join `from test, (select @num:=-1) num`. The `(select @num:=-1) num` part is a single value. This generates a cartesian product, therefore you need the 12+ row in the table. It is not required to use this table. You can make a brand new table with 12 empty columns, since the data in the table is not used. – Laposhasú Acsa Sep 05 '17 at 09:22
  • sorry sir but mistakenly i have added next 12 month in my expected result actually i want previous 12 month. So what to do for that. I have update my question sir. – always-a-learner Sep 05 '17 at 09:44
  • I've edited my answer, adding an explanation for how you can manipulate the month sequence. – Laposhasú Acsa Sep 05 '17 at 09:52
  • i have added my query fiddle in the question in which i have added my latest tried query. – always-a-learner Sep 05 '17 at 10:02
  • I think i provided you the full answer. What question do you have yet? – Laposhasú Acsa Sep 05 '17 at 10:12
  • sorry sir if i may irritate you. I have update the question sir. can you help for that i have added query fiddle. – always-a-learner Sep 05 '17 at 10:14
  • You don't irritate me, I just don't know what can I do for you. You can't leave the `dt` subquery. Take another look on the fiddle in my answer. – Laposhasú Acsa Sep 05 '17 at 10:18