Null Result Coming in query

Question

Good morning,

I need a help from you, I have a query in my system is with a problem, it brings 4 results, only 1 of these 4 is coming with null value.

I will post my query below

SELECT Title, SUM(Count) AS Count
FROM (
    SELECT
    CASE WHEN EstadoId = EstadoIdAutor AND EstadoId = Meddoc1 AND EstadoId = Meddoc2 AND EstadoId = Meddoc3 THEN
            'Sem deslocamento'
            ELSE CASE WHEN EstadoId = EstadoIdAutor AND (EstadoId = Meddoc1 OR EstadoId = Meddoc2 OR EstadoId = Meddoc3) THEN
            'Fora de domicílio do autor'
            ELSE CASE WHEN EstadoId <> EstadoIdAutor OR (EstadoId = EstadoIdAutor AND (EstadoId <> Meddoc1 AND EstadoId <> Meddoc2 AND EstadoId <> Meddoc3)) THEN
            'Mais de um deslocamento'
            END
            END
    END AS Title,
    COUNT(*) AS Count
    FROM jud_Processos
    WHERE Status = 1 AND Trancado = 1
    GROUP BY EstadoId, EstadoIdAutor, Meddoc1, Meddoc2, Meddoc3
) Sub
GROUP BY Title

Could someone help me? Where are you NULL come as Other

Answer 1

You just need to add the ELSE in his CASE:

SELECT title,
       SUM(count) AS count
  FROM (SELECT CASE
                 WHEN estadoid = estadoidautor
                      AND estadoid = meddoc1
                      AND estadoid = meddoc2
                      AND estadoid = meddoc3 THEN 'Sem deslocamento'
                 WHEN estadoid = estadoidautor
                      AND (estadoid = meddoc1
                       OR estadoid = meddoc2
                       OR estadoid = meddoc3) THEN 'Fora de domicílio do autor'
                 WHEN estadoid <> estadoidautor
                      OR (estadoid = estadoidautor
                      AND (estadoid <> meddoc1
                      AND estadoid <> meddoc2
                      AND estadoid <> meddoc3)) THEN 'Mais de um deslocamento'
                 ELSE 'Outros'
               END AS title,
               COUNT(*) AS count
          FROM jud_processos
         WHERE status = 1
           AND trancado = 1
         GROUP BY estadoid,
                  estadoidautor,
                  meddoc1,
                  meddoc2,
                  meddoc3) sub
 GROUP BY title 

Note: You only need one CASE in his query.

Answer 2

You can use the coalesce

SELECT COALESCE(Title,'OUTROS'), SUM(Count) AS Count
FROM (
    SELECT
    CASE WHEN EstadoId = EstadoIdAutor AND EstadoId = Meddoc1 AND EstadoId = Meddoc2 AND EstadoId = Meddoc3 THEN
            'Sem deslocamento'
            ELSE CASE WHEN EstadoId = EstadoIdAutor AND (EstadoId = Meddoc1 OR EstadoId = Meddoc2 OR EstadoId = Meddoc3) THEN
            'Fora de domicílio do autor'
            ELSE CASE WHEN EstadoId <> EstadoIdAutor OR (EstadoId = EstadoIdAutor AND (EstadoId <> Meddoc1 AND EstadoId <> Meddoc2 AND EstadoId <> Meddoc3)) THEN
            'Mais de um deslocamento'
            END
            END
    END AS Title,
    COUNT(*) AS Count
    FROM jud_Processos
    WHERE Status = 1 AND Trancado = 1
    GROUP BY EstadoId, EstadoIdAutor, Meddoc1, Meddoc2, Meddoc3
) Sub
GROUP BY Title