Count column data and reset after Cpf exchange

Question

I have the following columns in the table eventos (nome_hosp,cpf_hosp,parametro_evento, data).

What I need to do is, where the column value parametro_evento = 1, perform a line count by separating by CPF, that is, by people.

For example:

• If you have 03 data with CPF(fictional number) equal to 1

It will print me: CPF:1 total: 3

• If you have 03 data with CPF=1 and 02 data with CPF=2

He’ll print me out: CPF: 1 total:3 CPF: 2 total: 1

Table image

What’s been returning to me:

What I would like to return would be, in place of "Joao" the BPM would be only 1, because according to the database it only has an occurrence with parameter 1 and not 3.

Code:

$result_transacoes = "SELECT * FROM  eventos WHERE (timestamp_evento BETWEEN '$de' AND '$ate') GROUP BY cpf_hospede";
$resultado_trasacoes = mysqli_query($conecta, $result_transacoes);
$cont_bpm=0;
while($row_transacoes = mysqli_fetch_assoc($resultado_trasacoes)){
    if($row_transacoes['parametro_evento'] == '1') {
        $result = mysqli_query($conecta,"SELECT count(*) as total from eventos GROUP BY cpf_hospede");
        $data = mysqli_fetch_assoc($result);
        $num_bpms= $data['total'];
    }
}

I believed that if I made one group by that would solve my problem, but not.

Answer 1

From what I understand your problem is more in the matter of mounting SQL.

I created an example here, where you can test the solution

Example

Let’s imagine the following structure of your table

create table eventos(
  id integer,
  nome varchar(50),
  cpf varchar(50),
  parametro integer);

With the following records

 insert into eventos values (1, 'Joao', '123', 1);
 insert into eventos values (2, 'Joao', '123', 1);
 insert into eventos values (3, 'Joao', '123', 1);
 insert into eventos values (3, 'Joao', '123', 0);
 insert into eventos values (4, 'Maria', '321', 1);
 insert into eventos values (5, 'Maria', '321', 1);
 insert into eventos values (6, 'Matheus', '222', 1);

Now to bring the data you want, we can do everything in a single SQL, as follows

select nome
      ,cpf
      ,count(if(parametro = 1, 1, null)) as TOTAL
  from eventos
 group by cpf

That way you could get the result you want with a single SQL, just tailor your PHP programming now.

Explanation

count(if(parametro = 1, 1, null)) as TOTAL this part, will count all the records that have the parametro equal to 1, ignoring anything other than 1 setando null so that the function does not account for the same.

EDITED

I don’t have much knowledge with PHP and what I know is targeted with PDO, but this example can give you a light on how to apply to your case:

$result_transacoes   = "SELECT nome_hospede, cpf_hospede, count(if(parametro_evento = 1, 1, null)) as bpm FROM eventos WHERE (timestamp_evento BETWEEN '$de' AND '$ate') GROUP BY cpf_hospede";
$resultado_trasacoes = mysqli_query($conecta, $result_transacoes);

$total_bpm=0;
while($row_transacoes = mysqli_fetch_assoc($resultado_trasacoes)){
    $nome= $row_transacoes['nome_hospede'];
    $cpf= $row_transacoes['cpf_hospede'];
    $num_bpms= $row_transacoes['bpm'];

    $total_bpm+=$num_bpms;
}

Each time the loop comes, the variables will be implemented with the value referring to the current line of your query. The variable $total_bpm will be added with the BPM value of each line to return the total of Bpms for all records returned.