Cumulative count per month

Question

Cumulative count per month

Navigation

#1 by (0 votes)

0

I'm having trouble getting 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 trying to get select to return this 'total' for month_an:

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 of the forum I got the monthly count (not cumulative and without taking the exclusions) like this:

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 complete query for PHP and treat the information there (I do not know what is best for the performance as well) ... If someone knows how to help me . :)

mysql sql select

asked by anonymous 29.09.2018 / 16:47

1 answer

The app quits unexpectedly when I run the bank Google Maps API is not working in my application

score 0 · Accepted Answer

Opa,

I have a suggestion to try to solve your problem, however I am not familiar with the MySQL syntax (I searched the google for the right functions, but I could not test them). I will write the code below, explain why and you make possible adaptations and corrections to run right in your DBMS.

If you run the select without the part of the count, 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 do a subselect with count and compare dates according to the external select listing. The criteria are: The data_cadastro must be equal (month / year) and the date of exclusion (month / year) OR is null (ids 1,3,4,5) OR is greater than the one of cadastre (id 2, date_exclusion month / year, 08/2018, is greater than the data_cadastro 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 think it is more performative and simple to do the direct consultation in the bank, but it is a guess and personal opinion. If anyone has anything to contribute on this point it will be of great help.

Good luck!