0

I have table with following schema:

article(id,title, content, created_at, created_by)

I want to find all the records created by each user in each month. If the user did not create the the count should show 0. The date range should start from oldest record and end at most recent record. I want to group records by month-year, meaning I want to check records created by each user in each month, if a user did not create any record then it should NOT be skipped in result set rather it should show count for that month as 0.

Assume I have 5 records as follows:

id, title,      content,          created_at, created_by

1,  Some title, some description, 2017-04-21, 2

2,  Some title, some description, 2017-04-10, 2

3,  Some title, some description, 2017-07-15, 2

4,  Some title, some description, 2017-08-21, 2

5,  Some title, some description, 2017-10-04, 2

The result should be like this:

date,    count, created_by
2017-04,  2,     2
2017-05,  0,     2
2017-06,  0,     2
2017-07,  1,     2
2017-08,  1,     2
2017-09,  0,     2
2017-10,  1,     2

Please note that even if the user did not create any record in a specific month still that month-year was included, that is exactly what I want.

I want to do that in MySQL.

Thank You. And please don't ask what I have tried, I am not good at such complex queries.

2

I don't have a MySQL instance handy to test and confirm that this works for you, but this should work. It assumes that you have at least one entry for every month in the table.

SELECT DateTable.MonthDay
    , RecordCount.Records
    , RecordCount.created_by
FROM (
    SELECT DISTINCT DATE_ADD(LAST_DAY(DATE_SUB(created_at, interval 30 day), interval 1 day) AS MonthDay
    FROM article
    ) AS DateTable
    LEFT OUTER JOIN (SELECT DATE_ADD(LAST_DAY(DATE_SUB(created_at, interval 30 day), interval 1 day) AS MonthDay
                        , created_by
                        , COUNT(id) AS Records
                    FROM article
                    ) AS RecordCount ON RecordCount.MonthDay = DateTable.MonthDay
WHERE DateTable.MonthDay >= '4/1/2017' AND DateTable.MonthDay <= '10/1/2017'

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.