Get all months between two dates with count from table in MySql

Question

I have written query for get all month between two days with number of members as count but not able to get all month.

SET @start_date = '2017-08-31'; 
SET @end_date = '2018-06-26'; 
SELECT COUNT(1) AS Y, DATE_FORMAT(created_date,'%b %y') AS X 
FROM members m 
WHERE 
    created_date >= @start_date AND 
    created_date <= @end_date 
GROUP BY X 
ORDER BY X;

Following output I am getting

1   Feb 18
1   Mar 18
1   Nov 17
2   Oct 17

But expected result will be

2 Oct 17
1 Nov 17
0 Dec 17
0 Jan 18
1 Feb 18
1 Mar 18

Online Query LINK

You do not have data for the missing months, so they are not brought back in the query — Will, Jun 28 '19 at 15:13
@DjellalMohamedAniss i have mention LINK. you can find post data — ANJYR, Jun 28 '19 at 15:30
@will go with this url https://stackoverflow.com/q/14811568/2126088. Not necessary missing month data should be available. — ANJYR, Jun 28 '19 at 15:32

score 0 · Answer 1 · answered Jun 29 '19 at 03:29

Please execute the following SQL to get the desired output: select EXTRACT(YEAR FROM created_date) AS YEAR, EXTRACT(MONTH FROM created_date) AS MONTH, COUNT(1) from members GROUP BY YEAR, MONTH

Noticed that you applied sorting as well, kindly do the same as per your need, it will fetch the count with year and month number.

See the following link for more details on extract function: https://www.w3schools.com/sql/func_mysql_extract.asp

score -1 · Answer 2 · answered Jun 28 '19 at 15:54

You need a 'control' table for all the months you are trying to include. This can be done with a "calendar" table, or by including records as "placeholders" for all the months in the "members" table.

There is a better way to accomplish this, but, for an example, here's the idea (using data from your LINK above)

    CREATE TABLE members
(
    member_id INT,
    name VARCHAR(100),
    created_date DATE
);

INSERT INTO members (member_id, name, created_date) VALUES (1, 'Anand Jee', '2017/10/10');
INSERT INTO members (member_id, name, created_date) VALUES (2, 'Anil', '2017/11/21');
INSERT INTO members (member_id, name, created_date) VALUES (3, 'Saurav', '2018/2/10');
INSERT INTO members (member_id, name, created_date) VALUES (4, 'Amit', '2018/3/10');
INSERT INTO members (member_id, name, created_date) VALUES (5, 'Nilesh', '2018/7/17');
INSERT INTO members (member_id, name, created_date) VALUES (6, 'Nitesh', '2018/9/19');
INSERT INTO members (member_id, name, created_date) VALUES (7, 'Ajay', '2019/2/21');
INSERT INTO members (member_id, name, created_date) VALUES (8, 'Rahul', '2019/3/30');
INSERT INTO members (member_id, name, created_date) VALUES (9, 'Sumit', '2019/7/17');
INSERT INTO members (member_id, name, created_date) VALUES (10, 'Gautam', '2017/10/21');

INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2017/08/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2017/09/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2017/10/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2017/11/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2017/12/1');

INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2018/1/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2018/2/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2018/3/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2018/4/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2018/5/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2018/6/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2018/7/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2018/8/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2018/9/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2018/10/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2018/11/1');
INSERT INTO members (member_id, name, created_date) VALUES (-1, 'ZERO', '2018/12/1');

SET @start_date = '2017-08-31';
SET @end_date = '2018-06-26';
SELECT COUNT(1)-1 AS Y, DATE_FORMAT(created_date,'%b %y') AS X,DATE_FORMAT(created_date,'%y%m') AS Z
FROM members m
WHERE
created_date >= @start_date AND
created_date <= @end_date
GROUP BY X,Z
ORDER BY Z

Get all months between two dates with count from table in MySql

2 Answers2