2
   ###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 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

4
  1. Build a table of all months.
  2. LEFT JOIN to that table. Viola, the zeros show up.

Well, you might get NULLs instead of zeros. So use the IFNULL(application, 0) function.

BTW, MariaDB has a nifty way to generate sequences of numbers; see "seq".

2

Just group and order your IDs by both year and month:

SELECT date_format(tn.create_date,'%m') as Month, count(ID) as Application, date_format(tn.create_date,'%Y') as Year
FROM test tn
GROUP BY Year,Month
ORDER by Year,Month;

to get the data for last 12 month, you'll probably need to create Calendar table as it's already suggested by [~Rick James].

I'd make it with SP to create such table first:

CREATE
PROCEDURE .`Calendar`()
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS `Cal` (
`month` INT(11) ,
`year` INT(11) 
) ;

SET @counter := -1;
WHILE (DATEDIFF(DATE(NOW()), DATE(DATE_SUB(NOW(),INTERVAL @counter MONTH))) < DATEDIFF(DATE(NOW()), DATE(DATE_SUB(NOW(),INTERVAL 12 MONTH)))) DO 
INSERT INTO Cal SELECT DATE_FORMAT(DATE_ADD(DATE_SUB(NOW(),INTERVAL 12 MONTH), INTERVAL @counter:=@counter + 1 MONTH),'%m'),DATE_FORMAT(DATE_ADD(DATE_SUB(NOW(),INTERVAL 12 MONTH), INTERVAL @counter + 1 MONTH),'%Y');
END WHILE;
END

Than we can call it and run query on result:

CALL Calendar();

SELECT `Cal`.`Month`,IFNULL(COUNT(qr.ID),0) AS Application, `Cal`.`Year`
FROM `Cal`
LEFT JOIN ( SELECT ID,  MONTH(create_date) AS `Month`,  YEAR(create_date) AS `Year`
    FROM test_data AS tn
    WHERE create_date >  DATE_SUB(NOW(), INTERVAL 12 MONTH)
    ORDER BY create_date
) qr ON qr.`Month`=`Cal`.`Month`
GROUP BY YEAR,MONTH;
-1
select month(create_date), count(*) 
from t 
group by month(create_date), year(create_date);
  • 3
    I suggest, as a minimum expanding/reformatting your answer to include code formatting. meta.stackexchange.com/questions/22186/… can be helpful for this – Ian_H Sep 5 '17 at 11:59
  • 1
    Your answer seems to be the same as the one that isn't working in the question as you just added a second group by clause. – Joe W Sep 5 '17 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.