Select Group by Month

Question

SELECT dataVen, Sum(valorTotal) as valorTotal FROM cad_cr2 Group by MONTH(dataVen)

The above query returns the following values:

I wish it were so, grouped by month:

I have done many researches and attempts, but without success.

Answer 1

Just insert MONTH in the SELECT, gets like this:

SELECT MONTH(dataVen), SUM(valorTotal) AS valorTotal 
  FROM cad_cr2 GROUP BY MONTH(dataVen)

Answer 2

The function MONTH(data) receives a date as parameter and returns the month of the date reported.

You are already grouping your query by month and are adding the values grouped, now all you need to show the month in your SELECT:

SELECT MONTH(dataVen), 
       SUM(valorTotal) as valorTotal 
  FROM cad_cr2 
 GROUP BY MONTH(dataVen)

Observing: Your query is being grouped by the due date month only (dataVen), so you’re grouping even though the years are different. Example:

dataVen ..................... valorTotal

20/10/2005................. 5

05/10/2018 ................. 10

09/10/2018 ................. 15

In that case your return will be:

dataVen ..................... valorTotal

10 .............. 30

I don’t know about your business rule, but I’m dropping the hint because I imagine you want to filter only by the current year.

Answer 3

You can use the MONTH() or DATE_FORMAT(dataVen, '%m'). If you want to extract only the Month, Year or Day Mysql already has its own functions for this:

DAY(data) = para dia

MONTH(data) = para mes

YEAR(data) = para ano

SELECT MONTH(dataVen), SUM(valorTotal) AS valorTotal 
  FROM cad_cr2 GROUP BY MONTH(dataVen) 

---

SELECT DATE_FORMAT(dataVen, '%m'), SUM(valorTotal) AS valorTotal 
  FROM cad_cr2 GROUP BY MONTH(dataVen)