Group Month according to Between

Question

I created a statement in Mysql to display a graphic report, I wanted the months to be displayed from the month informed by the initial date to the month of the final date. But in my SELECT it groups the months and brings in a growing order

Sql statement

SELECT 
  MONTH(P.data_venda) as Meses, 
  SUM(P.vlr_tot_liquido) as Faturamento, 
  SUM(P.custo_total) as Custos, 
  SUM(P.vlr_tot_liquido) - SUM(P.custo_total) as Lucro 
  FROM pedido P
LEFT JOIN 
  (SELECT Min(pedido) as formapag_rec,
  empresa, filial, pedido, 
  plano as formapag_plano 
  FROM formapag
  WHERE empresa = 1
  GROUP BY 
  empresa, filial, pedido) 
  F ON F.empresa = P.empresa 
  and F.filial = P.filial 
  and F.pedido = P.pedido
LEFT JOIN 
   plano on plano.plano = formapag_plano
WHERE P.empresa = 1 
  AND P.data_venda BETWEEN '2015-08-01' AND '2016-04-01' 
  AND P.vlr_tot_liquido > 0
  AND P.status = 'FIN' 
  AND (plano.especie <> 'MOV' OR plano.especie is null)
GROUP BY Meses

If I report a month from last year to a month from another more recent year, my result is taken this way: the first months of the other year coming in front of the last months of the previous year.

I’d like it to be shown Months : 8, 9, 10, 11, 12, 1, 2, 3, 4 following a sequence.

Could someone help me ?

Answer 1

Basically you need to add the year to the grouping (for cases where your query brings an interval greater than a year, so the values will be specific to each month/year) and to the ordering (so that the order of display is from the oldest month to the most current):

SELECT 
  YEAR(P.data_venda) as Anos, 
  MONTH(P.data_venda) as Meses, 
  SUM(P.vlr_tot_liquido) as Faturamento, 
  SUM(P.custo_total) as Custos, 
  SUM(P.vlr_tot_liquido) - SUM(P.custo_total) as Lucro 
  FROM pedido P
LEFT JOIN 
  (SELECT Min(pedido) as formapag_rec,
  empresa, filial, pedido, 
  plano as formapag_plano 
  FROM formapag
  WHERE empresa = 1
  GROUP BY 
  empresa, filial, pedido) 
  F ON F.empresa = P.empresa 
  and F.filial = P.filial 
  and F.pedido = P.pedido
LEFT JOIN 
   plano on plano.plano = formapag_plano
WHERE P.empresa = 1 
  AND P.data_venda BETWEEN '2015-08-01' AND '2016-04-01' 
  AND P.vlr_tot_liquido > 0
  AND P.status = 'FIN' 
  AND (plano.especie <> 'MOV' OR plano.especie is null)
GROUP BY Anos, Meses
ORDER BY Anos, Meses

Notice I added the column Anos so that the year of that month may also be shown.