Total month to month with previous months

Asked

Viewed 789 times

4

I need a SELECTthat brings the sum of data month to month and disappears with the total of the previous months.

In the SELECT below is bringing the sum month by month.

SELECT DISTINCT MONTH(data_pgto_bx) as mes, SUM(total_bx) FROM gc_baixas
WHERE sit_bx = 3 GROUP BY mes ORDER BY mes ASC

So far so good with the SELECT above, my problem is a little more complex, I need this SELECT also add the column total_bx with previous months. Example:

  • In month 10 bring the column sum total_bx every month until the month 10;

  • In month 11 bring the sum of all months up to month 11;

mysql
  • 2

    In SQL Server we have classification functions to do this. In Mysql unfortunately this does not exist. You will have to use some logic using variables. Later I will try to answer your question.

  • Just to clarify, you need the monthly partial sum, right?

  • No Thomas, I need the total sum up to the month, and this select should show month by month with the overall total up to the month.

  • @Iriobroleisfilho that is, a field that informs the partial sum of the month, and that returns it in the period from month to month (monthly)

  • Thank you @Thomas. Omni’s response has met my need.

1 answer

5


Like the @gmsantos confirmed no analytical functions available in MySQL. The solution then is to run a query within the query which calculates the required results month by month:

SET @totalagregado := 0;
SELECT 
 resultados_mes.mes as mes,
 resultados_mes.soma_mes as soma_mes,
 (@totalagregado := @totalagregado + resultados_mes.soma_mes) as soma_agregada
FROM
(
  SELECT DISTINCT MONTH(data_pgto_bx) as mes, SUM(total_bx) as soma_mes
  FROM gc_baixas
  WHERE sit_bx = 3 
  GROUP BY mes
  ORDER BY mes ASC  
) as resultados_mes

Example in Sqlfiddle.

The result of this query shall have the following format::

Month | Total month | Aggregate total

1 | 1234 | 1234

2 | 1234 | 2468

...

  • 1

    Perfect Omni, this is what I needed. Thank you.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.