Select count distinct group by

[ndias]

Tenho uma tabela que gravo o idioma e a fluência do candidato. Por ex:

 

Candidato         Idioma           Fluência

1                        Inglês            1

2                        Espanhol      3

3                        Francês        2

 

Quero saber quantos candidatos tem inglês, espanhol e francês básico, avançado e intermediário.

 

Para isso, eu faço o seguinte:

 

select Idioma,
COUNT(distinct(Candidato)) as Total, 
count(CASE WHEN Fluência = 1 THEN 'Básico' ELSE NULL END) as Básico, 
count(CASE WHEN Fluência = 2 THEN 'Intermediário' ELSE NULL END) as Intermediário, 
count(CASE WHEN Fluência = 3 THEN 'Avançado' ELSE NULL END) as Avançado 
from tabela
group by Idioma
 

O resultado é:

 

Idioma            Total          Básico        Intermediário       Avançado

Inglês              2               2                 0                         0

Espanhol        3                1                 1                         1

Francês          1                0                 1                          0

 

Porém, se tiver um registro duplicado, ou seja, de dois candidatos, se um aparecer 3 vezes e outro 2 vezes na tabela com o Inglês básico, o resultado vai ser assim:

 

Idioma            Total          Básico        Intermediário       Avançado

Inglês              2               5                 0                         0

Espanhol        3                1                 1                         1

Francês          1                0                 1                          0

 

Como eu faço nesse caso para contar somente os registros únicos?

[Marcos Roberto Vieira Juni]

Buenas companheiro,

 

Montei um exemplo abaixo espero que ajuda, não sou muito bom com PIVOT mas segue ai.

DECLARE @table TABLE(
Candidato INT,
Idioma VARCHAR(100),
Cod_idioma INT,
Fluencia INT

)
-- Ingles = 1, Espanhol= 2, Frances = 3
INSERT INTO @table SELECT 1,'Ingles',1,1
INSERT INTO @table SELECT 4,'Ingles',1,3
INSERT INTO @table SELECT 7,'Ingles',1,2
INSERT INTO @table SELECT 2,'Espanhol',2,3
INSERT INTO @table SELECT 5,'Espanhol',2,2
INSERT INTO @table SELECT 8,'Espanhol',2,1
INSERT INTO @table SELECT 10,'Espanhol',2,1
INSERT INTO @table SELECT 11,'Espanhol',2,3
INSERT INTO @table SELECT 3,'Frances',3,2
INSERT INTO @table SELECT 6,'Frances',3,1
INSERT INTO @table SELECT 9,'Frances',3,2
INSERT INTO @table SELECT 12,'Frances',3,2
INSERT INTO @table SELECT 13,'Frances',3,2

SELECT  idioma,([1] + [2] + [3]) as TOTAL ,[1] AS 'Básico', [2] AS 'Intermediário',[3] AS 'Avançado'
FROM (SELECT idioma,Cod_idioma,Fluencia FROM @table ) U
PIVOT (COUNT(Cod_idioma) FOR Fluencia IN ([1],[2],[3]) ) P

 

 

Abraços,