How to redeem all data of the current month grouped by date?

Question

I have the following table:

+------------+--------------+-------------------+
|     ID     |    vacina    |       data        |
+------------+--------------+-------------------+
|     1      |    Cinomose  |2017-07-10 10:11:15|
+------------+--------------+-------------------+
|     2      | Coronavirose |2017-08-09 10:11:15|
+------------+--------------+-------------------+
|     3      |   Vermifugo  |2017-10-10 10:11:15|
+------------+--------------+-------------------+
|     4      | Anti-pulgas  |2017-07-25 10:11:15|
+------------+--------------+-------------------+
|     5      | Anti-rábica  |2017-06-06 10:11:15|
+------------+--------------+-------------------+
|     6      |      V4      |2017-07-10 10:11:15|
+------------+--------------+-------------------+ 

I would like a query that returns me the amount of vaccines you have on the day compared to the current month, considering that today is 2017-07-25. For example:

+------------+-------------------+
|    qnd     |       data        |
+------------+-------------------+
|     2      |2017-07-10 10:11:15|
+------------+-------------------+
|     1      |2017-07-25 10:11:15|
+------------+-------------------+

My initial initiative was this way below:

SELECT COUNT(*) as qnd, datetime_start as date FROM `tbl_delivery` GROUP BY datetime_start

But so does the process of counting and grouping but returns data of every month that are contained in the table.

Try using that question from: how to search for records saved in the current week, but the grouping did not function.

What would be the most viable way to rescue all data of the current month grouped by date?

Answer 1

The idea would be the same as the current week, just use the function MONTH Mysql to check the month and compare to the current date, CURRENT_DATE.

  SELECT COUNT(*) as `qtd`,
         `datetime_start` as `date`
    FROM `tbl_delivery`
   WHERE MONTH(`datetime_start`) = MONTH(CURRENT_DATE())
GROUP BY `datetime_start`

Answer 2

Anderson’s answer is correct, but one caveat to make is the processing of the data. In a table with many records the query will be slower due to comparison

WHERE MONTH(`datetime_start`) = MONTH(CURRENT_DATE())

Because all bank records will be compared in search of the current month MONTH(CURRENT_DATE()), making the search more granular.

If possible, try to pass the range of dates you need with the parameter BEETWEEN.

SELECT 
    COUNT(primary_key) as qtd, DAY(datetime_start)
FROM
    tbl_delivery
WHERE
    datetime_start BETWEEN '2017-06-01' AND '2017-06-30'
    group by DAY(datetime_start)

Following comparison:

Answer 3

You must use a Where clause in your code before group by:

WHERE datetime_start BETWEEN value1 AND value2;

if you only want the current week you can use this way:

WHERE datetime_start BETWEEN (sysdate-7) AND sysdate;

already in the case of the month can twist the -7 by -30