SQL - Count number of records between rows

Question

Staff need a help in creating a query.
I have a chart that ranks people per week.

PessoaId  | Semana  | Grupo 
----------------------------  
1         | 1       | 1   
1         | 2       | 1  
1         | 3       | 1  
1         | 4       | 1    
2         | 1       | 1  
2         | 2       | 2  
2         | 3       | 2
2         | 4       | 1
3         | 1       | 2  
3         | 2       | 2  
3         | 3       | 2
3         | 4       | 2

I need a query to count how many weeks they were out of group 1 and/or how many weeks ago they were out of group 1. This week by week.
Result wanted more or less like this

 
PessoaId  | Semana  | Tempo Fora
----------------------------------  
1         | 1       | 0   
1         | 2       | 0  
1         | 3       | 0  
1         | 4       | 0  
2         | 1       | 0  
2         | 2       | 1  
2         | 3       | 2
2         | 4       | 0
3         | 1       | 1  
3         | 2       | 2  
3         | 3       | 3
3         | 4       | 4

Answer 1

You can use the function GROUP BY along with a SUM and a IF.

SELECT PessoaId, Semana, SUM(IF(Grupo = 1, 0, 1)) 'Tempo Fora' FROM Classificacao
GROUP BY PessoaId, Semana

In that query I assumed your flame table Classificacao, if that is not the name replace by the correct name.

Answer 2

Juliano, if I understand correctly, every time the group is not equal to 1, account as missing, to get this information run the query below, if not run, let me know which platform is running the query itself:

  SELECT COUNT(*) as 'Faltou durante', PESSOAID as 'Cód. Pessoa' 
  FROM nometabela
  where grupo != 1
  group by pessoaid