0
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. :)
Thank you, Marcelobambino! It worked as I needed it. I will study more about subselect. I only had to make an adjustment in the code (>=), of:
where DATE_FORMAT(c1.data_cadastro,'%Y-%m') = DATE_FORMAT(c2.data_cadastro,'%Y-%m')
for:where DATE_FORMAT(c1.data_cadastro,'%Y-%m') >= DATE_FORMAT(c2.data_cadastro,'%Y-%m')
.– Jean Barbosa
Oops, ball show that worked. Thanks for the return!
– MarceloBambino