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.
Simply insert MONTH
into SELECT
, look like this:
SELECT MONTH(dataVen), SUM(valorTotal) AS valorTotal
FROM cad_cr2 GROUP BY MONTH(dataVen)
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.
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)