problem with select case

Asked

Viewed 89 times

2

I am selecting with case comparing different status of the same field and counting them in separate columns. The problem is that the first column is NULL until the list of the second finishes counting. After that is reversed.

My query is the following:

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 example of result:

resultado da consulta

Some help, if you please?

sql postgresql select case

  • "The problem is that the first column is NULL until the list of the second one finishes the count. Then this is reversed" John, I don’t understand, can you explain better? How to finish the count? The result comes all at once, I didn’t understand what you meant

  • I adjusted the post there with a photo. I ended up not expressing myself correctly at first, but I think with the image will be easier to understand.

  • now yes understood :) you want in the same line finalized and canceled right?

  • exactly! That’s the point. I’ll try to be more objective in the next

  • only one question, is not in your select, but joins are being done right? If not by inner join, you’re doing in the where right? otherwise the result will go wrong

  • yes, I’m doing in the Where!

Show 1 more comment

3 answers

1

You can also make a subquery to calculate each status per driver, thus:

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 example working: http://sqlfiddle.com/#! 17/29af1/28

  • I got the resolution in another answer already. But I appreciate the help. I’m trying to figure out how and ends topic or something like.

  • Close topic? No need, just choose an answer :)

0


All you have to do is increase the output 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

  • I returned to answer and the answer was already here. I did exactly that and solved my problem! I thank you very much, anyway.

  • You are welcome, always at your beck and call! Take advantage and give an UP in the answers you considered useful! Thanks.

0

You can turn this query into a subconsultation, grouping the return by fields that are not summations; thus, it will bring 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

Browser other questions tagged sql postgresql select case

You are not signed in. Login or sign up in order to post.