How do i make a sql query using more than one tables with onde condition for each?

i want to get some data from a table but separated by different conditions, for example how many lines have a column are equal to 1, how many have a column equal and the same until the number 5. i did this:

Select Count(E.ID) as emergencia,
Count(M.ID) as muitourgente,
count(U.ID) as urgente,
count (PU.ID) as poucourgente,
count (MU.ID) as menosurgente 
From ConsultaMedica as E,
ConsultaMedica as M,
consultaMedica as U,
consultamedica as PU,
consultamedica as MU

WHERE E.ID_gravidade = 1 And M.ID_gravidade = 2 AND U.ID_gravidade = 3 AND PU.ID_gravidade = 4 AND MU.ID_gravidade = 5;

my table is “ConsultaMedica” and i want to get how many times the numbers 1, 2, 3, 4 and 5 appears in column “ID_Gravidade” in just one query.

my result is 0|0|0|0|0 but i expected 1|0|0|0|0 because i have one line with the column “ID_Gavidade” equal to 1, and no columns with the other numbers.

Answer

Do not cross join. You can just use conditional aggregation instead:

select 
    sum(case when ID_gravidade = 1 then 1 else 0 end) as emergencia,
    sum(case when ID_gravidade = 2 then 1 else 0 end) as muitourgente,
    sum(case when ID_gravidade = 3 then 1 else 0 end) as urgente,
    sum(case when ID_gravidade = 4 then 1 else 0 end) as poucourgente,
    sum(case when ID_gravidade = 5 then 1 else 0 end) as menosurgente
from ConsultaMedica
where ID_gravidade between 1 and 5

Note: if all values of ID_gravidade are between 1 and 5, you can remove the where clause.