Select Group by Month

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

The above query returns the following values:

  

Iwishitwerethatway,groupedbymonth:

  

I've done a lot of research and try, but without success.

    
asked by anonymous 28.12.2018 / 19:57

3 answers

2

Simply insert MONTH into SELECT , look like this:

SELECT MONTH(dataVen), SUM(valorTotal) AS valorTotal 
  FROM cad_cr2 GROUP BY MONTH(dataVen)
    
28.12.2018 / 20:19
0

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

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

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

Note: Your query is only being grouped by month of expiration date (dataVen) , so you are grouping even if the years are different. Example:

  

dataVen ..................... ValueTotal

     

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

     

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

     

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

In this case your return will be:

  

dataVen ..................... ValueTotal

     

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

I do not know your business rule, but I'm leaving the tip because I imagine you want to filter only for the current year.

    
28.12.2018 / 20:55
-1

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

  

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)
    
28.12.2018 / 20:28