Get a "count" on a database using "where" to set a limit on the date

Question

I have a database in which i have a table to save reports . Each report haves a date (year-month-day) which is set whenever the report got created .

After a lot of tests i got something to work but just not as i would like it to work.

I want to get the quantity of reports that were made on every month from an initial date (year-month-day) to a final date (year-month-day). But i'm not quite sure how to get it done.

This is the MySQL sentence i'm using right now:

SELECT meses.month id_mes, count(re_fecha) total
       FROM
       (
               SELECT 1 AS MONTH
                  UNION SELECT 2 AS MONTH
                  UNION SELECT 3 AS MONTH
                  UNION SELECT 4 AS MONTH
                  UNION SELECT 5 AS MONTH
                  UNION SELECT 6 AS MONTH
                  UNION SELECT 7 AS MONTH
                  UNION SELECT 8 AS MONTH
                  UNION SELECT 9 AS MONTH
                  UNION SELECT 10 AS MONTH
                  UNION SELECT 11 AS MONTH
                  UNION SELECT 12 AS MONTH

       ) as meses

LEFT JOIN reportes ON month(re_fecha)  = meses.MONTH
WHERE re_fecha BETWEEN '2017-01-01' AND '2017-08-31'
GROUP BY meses.MONTH, monthName(re_fecha)

This is the following result i'm getting with the MySQL sentence:

id_mes  | total
---------------
  04    | 15
  05    | 5 
  06    | 15
  07    | 2

I'm not sure if this helps in any way, but if i don't use the "where re_fechas... " i get a result that is closer to what we look for:

id_mes  | total         
-------------           
01      | 0         
02      | 0         
03      | 0         
04      | 15            
05      | 5
06      | 15
07      | 2
08      | 6
09      | 0
10      | 0
11      | 0
12      | 0

And finally, what i would like to see:

id_mes       | total            
-------------------     
01-2017      | 0            
02-2017      | 0             
03-2017      | 0            
04-2017      | 15
05-2017      | 5
06-2017      | 15
07-2017      | 2
08-2017      | 6

I have two problems with how it works now:

1. When i use the sentence "where" the months that have 0 reports on the specified dates, are not shown. If i do not use "where", i get the things almost in the way i want them, but not in the range of dates i want.

2. The other issue i had is i would like to get the year of the month (As shown in the desired code block above).

I hope this is enough information to understand everything, i'm not sure if i could provide the database, but if you think that would help, let me know.

Answer 1

In your query try to change WHERE to AND.

Answer 2

You almost got it.

If you add OR re_fecha IS NULL to your WHERE clause, then you would got almost what you wanted.

I came up with another solution that can help you:

SELECT meses.aMonth aMonth, COUNT(re_fecha) total
FROM (
  -- Listing all months in period
  SELECT DATE_FORMAT(m1, '%m-%Y') aMonth
  FROM (
    -- Range limit: about 21 years
    SELECT
    ('2017-01-01' - INTERVAL DAYOFMONTH('2017-01-01')-1 DAY) +INTERVAL m MONTH as m1
    FROM (
      SELECT @rownum:=@rownum+1 m FROM
      (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
      (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
      (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
      (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4,
      (SELECT @rownum:=-1) t0 ) d1
    ) d2 
  WHERE m1 <= '2017-08-31'
  ORDER BY m1) meses
LEFT JOIN reportes ON DATE_FORMAT(re_fecha, '%m-%Y') = meses.aMonth
WHERE re_fecha BETWEEN '2017-01-01' AND '2017-08-31'
OR re_fecha IS NULL
GROUP BY meses.aMonth;

Test it: http://sqlfiddle.com/#!9/d21de6/27

Example output:

aMonth    total
01-2017   0
02-2017   0
03-2017   0
04-2017   15
05-2017   5
06-2017   0
07-2017   2
08-2017   0

If you wasn't using MySQL, then you could use a FULL OUTER JOIN instead of LEFT JOIN.

Keep in mind that this solution is limited to 21 years. Try it changing only the initial date to 1970 and see it for yourself.
If needed, add more (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) tn, to increase the number of months.

References and useful links:

MySQL monthly Sale of last 12 months including months with no Sale
How to get a list of months between two dates in mysql
How to do a FULL OUTER JOIN in MySQL?