Doubt about SQL - Group by (beginner)

Asked

Viewed 58 times

2

I have two tables, one with the names of doctors and the other with the classifications of the appointments and I want to list the doctors and the quantities of appointments based on the classifications. For that 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 having the clause clause Group by, it brings together different doctors. I believe that only a parentheses in the right place would already make the consultation work for different doctors, but I’m not getting to know what is this "right place".

I tried to add a

Distinct by a.TabAtendimentoSaida->TabMedicoId->TabPrestador->Codigo

at the beginning of the consultation, but it has been running for some time... I don’t believe it will work.

Could someone help me?

sql query group-by

1 answer

1


I believe that if you add CRM in group by it will already divide according to Cor 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

  • The problem is that CRM should be repeated. The result for individual query queries should bring me the CRM and doctor’s name for each of the Ratings he has attended and the count. If he attended 5 patients "blue" and 3 patients "yellow", the result will bring me two lines, both repeating the CRM and the doctor’s name and, in one of them the number of blue patients and in the other the number of yellow patients.

  • Yes, but if you add CRM in group by it will divide the information by color and by the respective CRM

  • I will try to run this way. Return in a few hours. Thank you!

  • It worked! Thank you very much! I will now give a study to understand why it worked... Hahaha

  • 1

    is simple, the group by serves to break up operations (Count, sum, avg). This way when informing only a field "Color" it will do the calculation by separating only by color, when adding another field it will have one more condition to perform calculations

Browser other questions tagged sql query group-by

You are not signed in. Login or sign up in order to post.