How do I retrieve all data from the current month grouped by date?

Navigation
2

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 I have on the day compared to the current month, considering that today it 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 as follows: 

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

However, it does the process of counting and grouping but returns data for all the months that are contained in the table.

Try to use this question from: how to fetch records saved in the current week , but the grouping did not work.

What would be the most feasible way to retrieve all data from the current month grouped by date?

    
asked by anonymous 25.07.2017 / 14:56

3 answers

4

The idea would be the same as the current week, just use the MONTH MySQL function to check the month and compare with 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'
    
25.07.2017 / 15:10
3

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

WHERE MONTH('datetime_start') = MONTH(CURRENT_DATE())

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

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

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)

Here's a comparison:

    
25.07.2017 / 15:35
2

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

WHERE datetime_start BETWEEN value1 AND value2;

If you just want the current week, you can use it this way: 

WHERE datetime_start BETWEEN (sysdate-7) AND sysdate;

In the case of the month you can turn the -7 by -30

    
25.07.2017 / 15:04
Search script does not return results or errors Sum in sql update