COUNT + CASE
Preciso contar quantidade de pacientes por faixa etária, alguem me ajuda a colocar um count nessas faixas etarias
SELECT
case when trunc((current_date - ricadpac.nasc) / 365.25) <= 14 then
'0 a 14'
when trunc((current_date - ricadpac.nasc) / 365.25) between 15 and 28 then
'15 a 28'
when trunc((current_date - ricadpac.nasc) / 365.25) between 29 and 42 then
'29 a 42'
when trunc((current_date - ricadpac.nasc) / 365.25) between 43 and 56 then
'43 a 56'
when trunc((current_date - ricadpac.nasc) / 365.25) between 57 and 71 then
'57 a 71'
when trunc((current_date - ricadpac.nasc) / 365.25) between 72 and 86 then
'72 a 86'
when trunc((current_date - ricadpac.nasc) / 365.25) >= 87 then
'>=87'
end as faixa_idade),
ricadpac.sexo,
count(ricadpac.sexo) as qtd_sexo
FROM RECADATE
INNER JOIN ATCABECATEND ON atcabecatend.id = RECADATE.ID_ATCABECATEND
INNER JOIN ricadpac ON RICADPAC.ID = atcabecatend.id_ricadpac
LEFT JOIN ATCABECATEND_TBSENHAATEND ON atcabecatend_tbsenhaatend.ID_ATCABECATEND = ATcabecatend.ID
LEFT JOIN TBFLUXOATEND ON tbfluxoatend.ID_TBSENHAATEND = atcabecatend_tbsenhaatend.ID_TBSENHAATEND
AND tbfluxoatend.ACAO='C'
INNER JOIN tblocal ON TBLOCAL.ID=TBFLUXOATEND.id_tblocal
inner join tbunidad u on u.cod=recadate.unidade
WHERE RECADATE.UNIDADE IN (2,12,14,15,17,24,25,41)
and TBFLUXOATEND.ID_TBLOCAL IN (7,21,27)
AND CAST(ATCABECATEND.DATA_HORA_ENTRADA AS DATE) between :data1 and :data2
group by u.nome, ricadpac.nasc, ricadpac.sexo
ORDER BY u.nome
SELECT
case when trunc((current_date - ricadpac.nasc) / 365.25) <= 14 then
'0 a 14'
when trunc((current_date - ricadpac.nasc) / 365.25) between 15 and 28 then
'15 a 28'
when trunc((current_date - ricadpac.nasc) / 365.25) between 29 and 42 then
'29 a 42'
when trunc((current_date - ricadpac.nasc) / 365.25) between 43 and 56 then
'43 a 56'
when trunc((current_date - ricadpac.nasc) / 365.25) between 57 and 71 then
'57 a 71'
when trunc((current_date - ricadpac.nasc) / 365.25) between 72 and 86 then
'72 a 86'
when trunc((current_date - ricadpac.nasc) / 365.25) >= 87 then
'>=87'
end as faixa_idade),
ricadpac.sexo,
count(ricadpac.sexo) as qtd_sexo
FROM RECADATE
INNER JOIN ATCABECATEND ON atcabecatend.id = RECADATE.ID_ATCABECATEND
INNER JOIN ricadpac ON RICADPAC.ID = atcabecatend.id_ricadpac
LEFT JOIN ATCABECATEND_TBSENHAATEND ON atcabecatend_tbsenhaatend.ID_ATCABECATEND = ATcabecatend.ID
LEFT JOIN TBFLUXOATEND ON tbfluxoatend.ID_TBSENHAATEND = atcabecatend_tbsenhaatend.ID_TBSENHAATEND
AND tbfluxoatend.ACAO='C'
INNER JOIN tblocal ON TBLOCAL.ID=TBFLUXOATEND.id_tblocal
inner join tbunidad u on u.cod=recadate.unidade
WHERE RECADATE.UNIDADE IN (2,12,14,15,17,24,25,41)
and TBFLUXOATEND.ID_TBLOCAL IN (7,21,27)
AND CAST(ATCABECATEND.DATA_HORA_ENTRADA AS DATE) between :data1 and :data2
group by u.nome, ricadpac.nasc, ricadpac.sexo
ORDER BY u.nome
Marcos Batista
Curtidas 0
Respostas
Emerson Nascimento
18/04/2022
SELECT u.nome, ricadpac.sexo, count(ricadpac.sexo) as qtd_sexo, count(case when trunc((current_date - ricadpac.nasc) / 365.25) <= 14 then u.nome else null end) '0 a 14', count(case when trunc((current_date - ricadpac.nasc) / 365.25) between 15 and 28 then u.nome else null end) '15 a 28', count(case when trunc((current_date - ricadpac.nasc) / 365.25) between 29 and 42 then u.nome else null end) '29 a 42', count(case when trunc((current_date - ricadpac.nasc) / 365.25) between 43 and 56 then u.nome else null end) '43 a 56', count(case when trunc((current_date - ricadpac.nasc) / 365.25) between 57 and 71 then u.nome else null end) '57 a 71', count(case when trunc((current_date - ricadpac.nasc) / 365.25) >= 87 then u.nome else null end) '>=87' FROM RECADATE INNER JOIN ATCABECATEND ON atcabecatend.id = RECADATE.ID_ATCABECATEND INNER JOIN ricadpac ON RICADPAC.ID = atcabecatend.id_ricadpac LEFT JOIN ATCABECATEND_TBSENHAATEND ON atcabecatend_tbsenhaatend.ID_ATCABECATEND = ATcabecatend.ID LEFT JOIN TBFLUXOATEND ON tbfluxoatend.ID_TBSENHAATEND = atcabecatend_tbsenhaatend.ID_TBSENHAATEND AND tbfluxoatend.ACAO='C' INNER JOIN tblocal ON TBLOCAL.ID=TBFLUXOATEND.id_tblocal inner join tbunidad u on u.cod=recadate.unidade WHERE RECADATE.UNIDADE IN (2,12,14,15,17,24,25,41) AND TBFLUXOATEND.ID_TBLOCAL IN (7,21,27) AND CAST(ATCABECATEND.DATA_HORA_ENTRADA AS DATE) between :data1 and :data2 group by u.nome, ricadpac.sexo ORDER BY u.nome
GOSTEI 0