Group Month according to Between

Navigation
0

I created a MySQL statement to display a graphical report, I wanted the months to be displayed from the month reported by the start date to the month of the end 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 few months of the other year coming in front of the last months of the previous year.

I would like to see Months: 8, 9, 10, 11, 12, 1, 2, 3, 4 following a sequence.

Could anyone help me?

    
asked by anonymous 11.01.2018 / 13:45

1 answer

1

Basically, you need to add the year to the grouping (for cases where your query brings a range longer than a year, so the values will be specific to each month / year) and to the sort order (so that the display order is the month most old 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 that I added the Anos column so that the year of that month is also displayed.

    
11.01.2018 / 13:55
Android - How to call a method from another class from onCreate ()? I did not understand how the value (self) in "city.status = self" inside the function add_city ()