problem with select case

Question

problem with select case

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

2

I'm doing select with case comparing different statuses of the same field and counting them in separate columns. The problem is that the first column gets NULL until the list of the second ends the count. Then this is reversed.

My query is as follows:

select
    case when cor.status = 35 then count(cor.uid) end as finalizadas,
    case when cor.status = 50 then count(cor.uid) end as canceladas_motorista,
    mot.nome || ' ' || mot.sobrenome as nome,
    mot.celular,
    mot.email
from motoristas mot, corridas cor
where...

follows the result example:

Any help, please?

sql postgresql select case

asked by anonymous 31.08.2018 / 14:03

3 answers

1

You can also do a subquery to calculate each status per driver, like this:

select 
  (select count(cor.uid)
     from corridas cor
    where cor.idMotorista = mot.idMotorista
     and cor.status = 35) as finalizadas,
  (select count(cor.uid)
     from corridas cor
    where cor.idMotorista = mot.idMotorista
     and cor.status = 50) as canceladas_motorista,
mot.nome  
from motoristas mot

See the example working: link

31.08.2018 / 14:38

0

You can turn this query into a subquery , grouping the return into fields that are not summative; thus bringing the end result to the user.

select sum(finalizadas), sum(canceladas_motorista), nome, celular, email
(select
    case when cor.status = 35 then count(cor.uid) end as finalizadas,
    case when cor.status = 50 then count(cor.uid) end as canceladas_motorista,
    mot.nome || ' ' || mot.sobrenome as nome,
    mot.celular as celular,
    mot.email as email
from motoristas mot, corridas cor
where ...) as tabela_subconsulta
group by nome, celular, email

31.08.2018 / 14:09

Wordpress: multiple queries Avoid Duplication in the PHP + MySQLi Registry

score 0 · Accepted Answer

You just have to increase the result of both columns:

SELECT      SUM(CASE WHEN cor.status = 35 THEN 1 ELSE 0 END)    AS finalizadas
        ,   SUM(CASE WHEN cor.status = 50 THEN 1 ELSE 0 END)    AS canceladas_motorista
        ,   mot.nome || ' ' || mot.sobrenome                    AS nome
        ,   mot.celular
        ,   mot.email
FROM        motoristas mot, corridas cor
WHERE       ...
GROUP BY    mot.nome || ' ' || mot.sobrenome
        ,   mot.celular
        ,   mot.email