SQL - Count number of records between rows

Question

SQL - Count number of records between rows

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I need help with creating a query.
I have a table that ranks people by 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 have been out of group 1 and / or how many weeks ago they are out of group 1. This week a week. Result wanted more or less

 
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

mysql sql

asked by anonymous 25.01.2018 / 23:47

2 answers

Problem running Android How to display two tables in SQLDeveloper at the same time?

score 0 · Answer 1

0

>

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

26.01.2018 / 01:01

score 0 · Answer 2

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

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

In this query I assumed that your table of calls Classificacao , if not that name replace with the correct name.