Doubt with select case

Question

I have the following query:

select

f.no_equipe,
i.no_cbo,
count(b.tp_atend)


from

tb_equipe F,
tb_cds_prof G,
tb_cbo I,
tb_cds_ficha_atend_individual D,
tb_cds_atend_individual B

where

F.nu_ine = G.nu_ine and
G.nu_cbo_2002 = I.co_cbo_2002 and
D.co_cds_prof = G.co_seq_cds_prof and
B.co_cds_ficha_atend_individual = D.co_seq_cds_ficha_atend_indivdl

group by

F.no_equipe, I.no_cbo

I want to include two more fields:

1. count(where b.tp_atend = 1 ou 2)
2. count(where b.tp_atend = 3 ou 4)

I know I can do using CASE, but I don’t know how to create the command.

Answer 1

How do you want to make one COUNT conditional, it is necessary to use the SUM and within it a CASE, if the condition is met will add 1 or add 0.

select

f.no_equipe,
i.no_cbo,
count(b.tp_atend),
sum(
    CASE
        WHEN b.tp_atend IN (1, 2) THEN 1
        ELSE 0
    END
) as count1,
sum(
    CASE
        WHEN b.tp_atend IN (3, 4) THEN 1
        ELSE 0
    END
) as count2

from

tb_equipe F,
tb_cds_prof G,
tb_cbo I,
tb_cds_ficha_atend_individual D,
tb_cds_atend_individual B

where

F.nu_ine = G.nu_ine and
G.nu_cbo_2002 = I.co_cbo_2002 and
D.co_cds_prof = G.co_seq_cds_prof and
B.co_cds_ficha_atend_individual = D.co_seq_cds_ficha_atend_indivdl

group by

F.no_equipe, I.no_cbo