Cumulative count per month

Question

I’m having difficulty making a cumulative count in mysql. I need to count the amount of active registration accumulated in each month.

Table:

id | data_cadastro | data_exclusao
---+---------------+--------------
1  | 2018-07-01    | null
2  | 2018-07-02    | 2018-08-01
3  | 2018-08-01    | null
4  | 2018-08-02    | null
5  | 2018-08-03    | null

What I’m looking for the select to return this 'total' by month:

mes_ano | total   |   Descrição (só para entendimento...)
--------+-------  |  -------------------------------
2018-07 | 2       |   No mês 07 haviam 02 cadastros ativos (o id 2 foi excluído só no mês 08, então ele conta para o mês 07);
2018-08 | 4       |   No mês 08 haviam 04 (03 cadastros do mês 08 não excluídos + 01 cadastro ativo do mês 07);

With the help of other questions here from the forum I arrived at the monthly count (non-cumulative and without taking the exclusions) so:

SELECT 
  DATE_FORMAT(data_cadastro,'%Y-%m') AS 'mes_ano',
  COUNT(id) AS 'total'
FROM cadastro
GROUP BY DATE_FORMAT(data_cadastro,'%Y-%m')

I am in doubt if it is possible to do this criterion directly in SELECT or take the full query to PHP and handle the information there even (I do not know what is best for performance too)... If anyone can help me. :)

Answer 1

Opa,

I have a suggestion to try to solve your problem, but I’m not familiar with the syntax of Mysql (I googled the right functions, but I couldn’t test them). I will write the code below , explain why and you perform the possible adaptations and corrections to run right on your DBMS.

If you run select without the Count part, the result will be the months, right?

SELECT 
  DATE_FORMAT(data_cadastro,'%Y-%m') AS 'mes_ano'
 FROM cadastro
 GROUP BY DATE_FORMAT(data_cadastro,'%Y-%m')

So the idea is to make a subselect with Count and compare the dates according to the external select listing. The criteria are : The data_registration must be equal (month/year) and the date of exclusion (month/year) OR is null (ids 1,3,4,5) Or is higher than the registration (id 2, data_exclusion month/year, 08/2018, is higher than the data_registration month/year, 07/2018).

   SELECT 
  DATE_FORMAT(c1.data_cadastro,'%Y-%m') AS 'mes_ano',
 (select count(id)
    from cadastro as c2
  where DATE_FORMAT(c1.data_cadastro,'%Y-%m') = DATE_FORMAT(c2.data_cadastro,'%Y-%m')
     and (c2.data_exclusao is null or 
         DATE_FORMAT(c2.data_exclusao,'%Y-%m') 
       > DATE_FORMAT(c2.data_cadastro,'%Y-%m'))
       ) as total    
 FROM cadastro as c1
 GROUP BY DATE_FORMAT(c1.data_cadastro,'%Y-%m')

As for the doubt about performance, I can not say... I believe it is more performative and simple to perform the consultation directly at the bank, but it is a guess and personal opinion. If anyone has anything to contribute on that point it will be of great help.

Good luck!