Doubt about SQL-Group by (beginner)
I have two tables, one with names of doctors and one with the classifications does care and I want to list the doctors and quantities of care based on the classifications. For this I made the following query:
Select
a.TabAtendimentoSaida->TabMedicoId->TabPrestador->Codigo as CRM,
a.TabAtendimentoSaida->TabMedicoId->TabPrestador->Nome as Médico,
Case b.Prioridade->Cor
When '$00FF8000' Then 'AZUL'
When '$0000FF00' Then 'VERDE'
When '$0000FFFF' Then 'AMARELO'
When '$00FF00FF' Then 'SEM CLASSIFICADOR'
When '$000080FF' Then 'LARANJA'
When '$00000000' Then 'NÃO RESPONDEU'
When '$000000FF' Then 'VERMELHO'
Else Null
End as Cor,
Count(b.Prioridade->Cor) as Quantidade
From
Cliente.Atendimento a,
WClaRisco.Classificacao b
Where
a.TabAtendimentoSaida->TabMedicoId->TabPrestador->Codigo = :crm;
and a.Data between :datainicio; and :datafim;
and a.CodigoRa = b.Atendimento->Atendimento->CodigoRa
Group by b.Prioridade->Cor
Order by 4 DESC
And the consultation has been working properly, to bring me doctors individually. However, if I want her to bring me more than one doctor, by the appointment having the Group by clause, it groups different doctors. I believe that only one parentheses in the right place would already make the consultation work for different doctors, but I am not able to know what is this "right place".
I tried adding a
Distinct by a.TabAtendimentoSaida->TabMedicoId->TabPrestador->Codigo
At the beginning of the query, but it has been running for some time... I can't believe it's gonna work.
Could anyone help me?
1 answers
I believe that if you add CRM in group by it will already divide according to color and CRM
Select
a.TabAtendimentoSaida->TabMedicoId->TabPrestador->Codigo as CRM,
a.TabAtendimentoSaida->TabMedicoId->TabPrestador->Nome as Médico,
Case b.Prioridade->Cor
When '$00FF8000' Then 'AZUL'
When '$0000FF00' Then 'VERDE'
When '$0000FFFF' Then 'AMARELO'
When '$00FF00FF' Then 'SEM CLASSIFICADOR'
When '$000080FF' Then 'LARANJA'
When '$00000000' Then 'NÃO RESPONDEU'
When '$000000FF' Then 'VERMELHO'
Else Null
End as Cor,
Count(b.Prioridade->Cor) as Quantidade
From
Cliente.Atendimento a,
WClaRisco.Classificacao b
Where
a.TabAtendimentoSaida->TabMedicoId->TabPrestador->Codigo = :crm;
and a.Data between :datainicio; and :datafim;
and a.CodigoRa = b.Atendimento->Atendimento->CodigoRa
Group by b.Prioridade->Cor, a.TabAtendimentoSaida->TabMedicoId->TabPrestador->Codigo
Order by 4 DESC