Add Count to a query

Question

Good morning, I’m trying to sum up a Count in a query that returns three columns in the oracle the total diet column being the event count for that group!

NM_DIETA	TOTAL_DIETA	DS_SETOR
Extra: fruit	4	ANNEX A
Free or General Diet	2	ANNEX A

SELECT
  
    D.NM_DIETA,
    count(*) total_dieta,
    /*sum(count(*)) total  adicionar o total da contagem*/,
    obter_ds_setor_atendimento(s.cd_setor_atendimento) DS_SETOR
FROM 
    ATENDIMENTO_PACIENTE_V V
INNER JOIN CPOE_DIETA C ON (V.NR_ATENDIMENTO = C.NR_ATENDIMENTO)
INNER JOIN DIETA D ON (C.CD_DIETA = D.CD_DIETA)
INNER JOIN SETOR_ATENDIMENTO S ON (V.CD_SETOR_ATENDIMENTO = S.CD_SETOR_ATENDIMENTO)
WHERE S.CD_SETOR_ATENDIMENTO LIKE UPPER(:CD_SETOR_ATENDIMENTO)

AND V.DT_ALTA IS NULL
and c.dt_suspensao is null
and (c.dt_fim>sysdate or c.dt_fim is null)
GROUP BY 
    D.NM_DIETA,
    obter_ds_setor_atendimento(s.cd_setor_atendimento)
        
ORDER BY total_dieta desc

I receive the following information: ORA-00937: not a simple group function

How can I add the total count column?

NM_DIETA	TOTAL_DIETA	DS_SETOR	TOTAL
Extra: fruit	4	ANNEX A	6
Free or General Diet	2	ANNEX A	6

Answer 1

Simple solution using OVER()

SELECT
  
    D.NM_DIETA,
    count(*) total_dieta,
    SUM(COUNT(*)) OVER() total,
    obter_ds_setor_atendimento(s.cd_setor_atendimento) DS_SETOR
FROM 
    ATENDIMENTO_PACIENTE_V V
INNER JOIN CPOE_DIETA C ON (V.NR_ATENDIMENTO = C.NR_ATENDIMENTO)
INNER JOIN DIETA D ON (C.CD_DIETA = D.CD_DIETA)
INNER JOIN SETOR_ATENDIMENTO S ON (V.CD_SETOR_ATENDIMENTO = S.CD_SETOR_ATENDIMENTO)
WHERE S.CD_SETOR_ATENDIMENTO LIKE UPPER(:CD_SETOR_ATENDIMENTO)

AND V.DT_ALTA IS NULL
and c.dt_suspensao is null
and (c.dt_fim>sysdate or c.dt_fim is null)
GROUP BY 
    D.NM_DIETA,
    obter_ds_setor_atendimento(s.cd_setor_atendimento)
        
ORDER BY total_dieta desc

Upshot:

NM_DIETA	TOTAL_DIETA	DS_SETOR	TOTAL
Extra: fruit	4	ANNEX A	6
Free or General Diet	2	ANNEX A	6