Select returns 2 rows per GROUP BY account

Asked

Viewed 296 times

2

The code below returns the number of marked and the number of served, but when adding the CASE to bring me the amount of absences it separates the line of attendees from the line of the defaulters, this all on account of the group by that has to be done with the spine IAC.TP_PRESENCA_FALTA.

How do I make it not happen?

SELECT
    TRUNC(AC.DT_AGENDA)        AS DATA_AGENDA,
    ME.DS_MULTI_EMPRESA        AS EMPRESA,
    AC.CD_UNIDADE_ATENDIMENTO  AS CD_UNID_ATENDIMENTO,
    UA.DS_UNIDADE_ATENDIMENTO  AS UNIDADE_ATENDIMENTO,
    COUNT(IAC.NM_PACIENTE)     AS TOTAL_MARCADOS,
    COUNT(IAC.CD_ATENDIMENTO)  AS ATENDIDOS,
    (COUNT(IAC.CD_ATENDIMENTO) / COUNT(IAC.NM_PACIENTE) * 100) PERC_ATENDIDO,
    (CASE WHEN IAC.TP_PRESENCA_FALTA = 'F' THEN COUNT(IAC.TP_PRESENCA_FALTA) END) AS FALTA

FROM AGENDA_CENTRAL AC
    INNER JOIN IT_AGENDA_CENTRAL   IAC   ON AC.CD_AGENDA_CENTRAL      = IAC.CD_AGENDA_CENTRAL
    INNER JOIN UNIDADE_ATENDIMENTO UA    ON UA.CD_UNIDADE_ATENDIMENTO = AC.CD_UNIDADE_ATENDIMENTO
    INNER JOIN MULTI_EMPRESAS      ME    ON ME.CD_MULTI_EMPRESA       = AC.CD_MULTI_EMPRESA

WHERE IAC.NM_PACIENTE IS NOT NULL
AND ME.CD_MULTI_EMPRESA  = 2
AND AC.CD_AGENDA_CENTRAL = 12643

GROUP BY AC.CD_UNIDADE_ATENDIMENTO,
    UA.DS_UNIDADE_ATENDIMENTO,
    ME.DS_MULTI_EMPRESA,
    AC.DT_AGENDA,
    IAC.TP_PRESENCA_FALTA

Upshot:

MARCADOS    ATENDIDOS   PERC_ATENDIDO   FALTA
 9              9           100%          0
 4              0            0            4

Expected result:

MARCADOS    ATENDIDOS   PERC_ATENDIDO   FALTA
    13          9            69%          4
sql pl-sql

  • has 8 columns in select, and 4 in result... ?

  • The problem is not the columns, I only summarized because the number of columns are the same, what is occurring is that the result returns two lines, it separates the missed ones from the answered ones, I do not want it, I just want it to add the missing line the amount of missed ones. I explain this in the example below expected result.

  • precisely, the columns you are returning make the difference in the result with group by

  • Do you have any idea how I fix this?

  • first thing is to limit select only in the columns you need, if it is already so, put the full result being returned

  • 1

    One possibility in this case is to do the fault Count inside a subselect instead of doing it in the main select. The way it is structured at the moment, the group by is really necessary

  • basically, the result you’re having, is a line for each AC.CD_UNIDADE_ATENDIMENTO,
 UA.DS_UNIDADE_ATENDIMENTO,
 ME.DS_MULTI_EMPRESA,
 AC.DT_AGENDA,
 IAC.TP_PRESENCA_FALTA and, in one of these columns, a value is different from the other, so it does not group.

  • @Andrey following his idea really worked. Reply published.

Show 3 more comments

1 answer

1

Reply, based on @Andrey’s comment.

By making the subselect it was possible to return the expected result. Follow below the corrected code.

SELECT
    DATA_AGENDA,
    EMPRESA,
    CD_UNID_ATENDIMENTO,
    UNIDADE_ATENDIMENTO,
    TOTAL_MARCADOS,
    ATENDIDOS,
    TOTAL_MARCADOS - ATENDIDOS AS FALTA,
    ROUND(PERC_ATENDIDO,0) PERC_ATENDIDO


FROM (SELECT
      TRUNC(AC.DT_AGENDA)        AS DATA_AGENDA,
      ME.DS_MULTI_EMPRESA        AS EMPRESA,
      AC.CD_UNIDADE_ATENDIMENTO  AS CD_UNID_ATENDIMENTO,
      UA.DS_UNIDADE_ATENDIMENTO  AS UNIDADE_ATENDIMENTO,
      COUNT(IAC.NM_PACIENTE)     AS TOTAL_MARCADOS,
      COUNT(IAC.CD_ATENDIMENTO)  AS ATENDIDOS,
      (COUNT(IAC.CD_ATENDIMENTO) / COUNT(IAC.NM_PACIENTE) * 100) PERC_ATENDIDO
      ,(SELECT (CASE WHEN IT_AGENDA_CENTRAL.TP_PRESENCA_FALTA = 'F' THEN COUNT(IT_AGENDA_CENTRAL.TP_PRESENCA_FALTA) END) FALTA

       FROM IT_AGENDA_CENTRAL) FALTA



FROM AGENDA_CENTRAL AC
    INNER JOIN IT_AGENDA_CENTRAL   IAC   ON AC.CD_AGENDA_CENTRAL      = IAC.CD_AGENDA_CENTRAL
    INNER JOIN UNIDADE_ATENDIMENTO UA    ON UA.CD_UNIDADE_ATENDIMENTO = AC.CD_UNIDADE_ATENDIMENTO
    INNER JOIN MULTI_EMPRESAS      ME    ON ME.CD_MULTI_EMPRESA       = AC.CD_MULTI_EMPRESA

WHERE IAC.NM_PACIENTE IS NOT NULL
AND ME.CD_MULTI_EMPRESA  = 2
AND AC.CD_AGENDA_CENTRAL = 12643

Removed from IAC.TP_PRESENCA_FALTA from GROUP BY

GROUP BY AC.CD_UNIDADE_ATENDIMENTO,
    UA.DS_UNIDADE_ATENDIMENTO,
    ME.DS_MULTI_EMPRESA,
    AC.DT_AGENDA)

  • Damn, you helped me with that one too.

Browser other questions tagged sql pl-sql

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