Create a query with grouping by month

Question

Create a query with grouping by month

Navigation

#1 by (3 votes)
#2 by (0 votes)

0

I have a field in my database (dt_nf) which is type DATE. I would like to do a query that groups not by date, but by month and year, but I can not. My code looks 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

php query

asked by anonymous 07.11.2018 / 10:59

2 answers

0

Good morning. It seems to me that if you want to group by the two variables you have them in group by and have them as a column in select , it can 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))

07.11.2018 / 11:13

Unwanted white space CSS Is this a Python 3.6 bug?

score 3 · Accepted Answer

There are two ways to do the following:

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

or else you make an extract of the month and year of the date field so

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

and group by will stay

GROUP BY mesano