Create a grouped query by month

Question

I have a field in my database (dt_nf) that is DATE type. I would like to do a query that groups not by date, but by month and year, but I’m not getting it. My code is like this:

SELECT 
Sum(sjy_vendas.qtv) AS qt, 
Sum(sjy_vendas.rprd) AS rec, 
Sum(rprd-tprd-vlr_compra) AS lucro
FROM sjy_type 
INNER JOIN sjy_empresas 
INNER JOIN sjy_vendas ON sjy_empresas.id_empresa = sjy_vendas.empresa
AND sjy_type.id_tipo = sjy_vendas.tipo
WHERE sjy_type.dep='1' 
AND sjy_empresas.grupo='$grupo' 
AND sjy_vendas.dt_nf BETWEEN '$inicio' AND '$final'
AND sjy_vendas.empresa LIKE '$emp'
GROUP BY MONTH(sjy_vendas.dt_nf) AS dt_nf
ORDER BY sjy_vendas.dt_nf

Answer 1

there are two ways to do follow the ex:

GROUP BY DATE_FORMAT(sjy_vendas.dt_nf,'%Y-%m')

or else you make a month and year Xtract of the date field like this

SELECT EXTRACT(YEAR_MONTH FROM sjy_vendas.dt_nf) mesano 
    FROM ...

and group by will stay

GROUP BY mesano 

Answer 2

Good morning. It seems to me that if you want to group by the two variables you must have them in the group by and have them as a pillar in select, might be something like:

SELECT 
Sum(sjy_vendas.qtv) AS qt, 
Sum(sjy_vendas.rprd) AS rec, 
Sum(rprd-tprd-vlr_compra) AS lucro
concat(MONTH(sjy_vendas.dt_nf) , '/',YEAR(sjy_vendas.dt_nf)) as mesAno
...
GROUP BY concat(MONTH(sjy_vendas.dt_nf) , '/',YEAR(sjy_vendas.dt_nf))