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?

Author: Orley Lima, 2020-01-22

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
 1
Author: Marcos ACR, 2020-01-22 14:21:59