Getting the result of sum / count with two different conditions

I’ve been trying to extract to average times (based on some criteria). I need to get two results. These two results are based on how many minutes between two times, divided by the quantity of registers in the table. So, it’s basically like this:

select round(sum(a.dt_atend_medico-a.dt_entrada)/count(a.nr_atendimento),0)tempopsa
from atendimento_paciente a JOIN atend_paciente_unidade b ON a.nr_atendimento = b.nr_atendimento AND b.nr_sequencia = 1
JOIN setor_atendimento c ON b.cd_setor_atendimento = c.cd_setor_atendimento
where c.cd_setor_atendimento = 65
and a.dt_cancelamento IS NULL
AND a.dt_alta IS NULL
AND a.ie_tipo_atendimento = 3
AND a.dt_entrada between (sysdate)-1/24 and sysdate

I need to extract the second result, and the only different value is “c.cd_setor_atendimento”, which is 66. I’ve tried using case when, but then it makes me group the results:

SELECT case when a.dt_atend_medico is not null and c.cd_setor_atendimento = 65 then
round(sum(Obter_min_entre_datas(a.dt_entrada, a.DT_ATEND_MEDICO,0))/count(a.nr_atendimento),0) end as tempopsa,
case when a.dt_atend_medico is not null and c.cd_setor_atendimento = 66 then
round(sum(Obter_min_entre_datas(a.dt_entrada, a.DT_ATEND_MEDICO,0))/count(a.nr_atendimento),0) end as tempopsi
from atendimento_paciente a JOIN atend_paciente_unidade b ON a.nr_atendimento = b.nr_atendimento AND b.nr_sequencia = 1
JOIN setor_atendimento c ON b.cd_setor_atendimento = c.cd_setor_atendimento
where 
a.dt_cancelamento IS NULL
AND a.dt_alta IS NULL
AND a.ie_carater_inter_sus = 02
AND a.ie_tipo_atendimento = 3
AND a.dt_entrada between (sysdate)-1/24 and sysdate

That gives me ORA-00937: not a single-group group function 00937. 00000 – “not a single-group group function”

Any tips to get these two values with different parameters? (I need to get then as separated values, so I can’t use UNION between two selects)

Thanks!

Answer

The concept of conditional aggregation, which you are trying to perform here, is to put the case expressions within the aggregate functions rather than around them – and the rest of your query remains unchanged:

select
    round(
        sum(
            case when a.dt_atend_medico is not null and c.cd_setor_atendimento = 65 
            then Obter_min_entre_datas(a.dt_entrada, a.DT_ATEND_MEDICO,0) end
        )
        / count(
            case when a.dt_atend_medico is not null and c.cd_setor_atendimento = 65 
            then a.nr_atendimento end
        ), 0) as tempopsa,
    round(
        sum(
            case when a.dt_atend_medico is not null and c.cd_setor_atendimento = 66 
            then Obter_min_entre_datas(a.dt_entrada, a.DT_ATEND_MEDICO,0) end
        )
        / count(
            case when a.dt_atend_medico is not null and c.cd_setor_atendimento = 66 
            then a.nr_atendimento end
        ), 0) as tempopsi
from ...