SQL to count records that have a field

Question

Good afternoon,

I don’t know how the rules of the topic work, I asked for help on a previous topic and one of the members ended up helping me only that I wanted to ask another question about the help.

The query he helped me was below and is correct

SELECT
  SUM(CASE 
    WHEN Pressaus LIKE '0,%' THEN 1
    WHEN Pressaus LIKE '%,0' THEN 1
    WHEN Pressaus LIKE '%,0,%' THEN 1
    WHEN Pressaus = '0' THEN 1
    ELSE 0
  END) AS 'Apoio Profissional',
  SUM(CASE 
    WHEN Pressaus LIKE '1,%' THEN 1
    WHEN Pressaus LIKE '%,1' THEN 1
    WHEN Pressaus LIKE '%,1,%' THEN 1
    WHEN Pressaus = '1' THEN 1
    ELSE 0
  END) AS 'Cirurgia',
  SUM(CASE 
    WHEN Pressaus LIKE '2,%' THEN 1
    WHEN Pressaus LIKE '%,2' THEN 1
    WHEN Pressaus LIKE '%,2,%' THEN 1
    WHEN Pressaus = '2' THEN 1
    ELSE 0
  END) AS 'Consulta Médica',
  SUM(CASE 
    WHEN Pressaus LIKE '3,%' THEN 1
    WHEN Pressaus LIKE '%,3' THEN 1
    WHEN Pressaus LIKE '%,3,%' THEN 1
    WHEN Pressaus = '3' THEN 1
    ELSE 0
  END) AS 'Custeio',

  SUM(CASE 
    WHEN Pressaus LIKE '4,%' THEN 1
    WHEN Pressaus LIKE '%,4' THEN 1
    WHEN Pressaus LIKE '%,4,%' THEN 1
    WHEN Pressaus = '4' THEN 1
    ELSE 0
  END) AS 'Exame Médico'
FROM jud_Processos;

This query would be posted to a chart of my system. This query below is the old query of my system.

SELECT
                        CASE Tipo
                            WHEN 0 THEN 'Apoio Profissional'
                            WHEN 1 THEN 'Cirurgia'
                            WHEN 2 THEN 'Consulta Médica'
                            WHEN 3 THEN 'Custeio'
                            WHEN 4 THEN 'Exame Médico'
                            WHEN 5 THEN 'Fitoterápico/Homeopático'
                            WHEN 6 THEN 'Fraldas/Lenços'
                            WHEN 7 THEN 'Internação Compulsória'
                            WHEN 8 THEN 'Internação Domiciliar'
                            WHEN 9 THEN 'Internação Não-Compulsória'
                            WHEN 10 THEN 'Leito'
                            WHEN 11 THEN 'Leito de UTI'
                            WHEN 12 THEN 'Medicamento'
                            WHEN 13 THEN 'Produto de Saúde'
                            WHEN 14 THEN 'Suplemento Alimentar'
                            WHEN 15 THEN 'Tratamento de Saúde'
                            WHEN 16 THEN 'UTI/Aérea'
                            WHEN 17 THEN 'UTI Móvel/Transferência Hospitalar'
                            WHEN 18 THEN 'Órtese/Prótese'
                            WHEN 19 THEN 'Procedimento Eletivo'
                            WHEN 20 THEN 'Outros'
                        END AS Title,
                        (Processos) AS Count
                        FROM (
                            SELECT p.Tipo,
                            (SELECT COUNT(*) FROM (
                                SELECT COUNT(*) AS Processos
                                FROM jud_Prodserv
                                WHERE Tipo = p.Tipo
                                GROUP BY ProcessoId
                            ) Sub) AS Processos,
                            COUNT(*) AS Count
                            FROM jud_Prodserv p
                            INNER JOIN jud_Processos pr ON p.ProcessoId = pr.ProcessoId
                            WHERE pr.Status = 1 AND pr.Trancado = 1 {dados}
                            GROUP BY Tipo
                        ) Sub
                        ORDER BY Tipo;

I would like to know if it can be based on WHERE has between Keys the word {dice} this key generates the system charts. it is possible to base on the old query of my system?

Answer 1

Without the diagram of the tables it is difficult to understand how the JOINS should work, but if your problem is to transpose the first query, you can exchange the CASE WHEN for SUBQUERIES united:

SELECT "Apoio Profissional" AS Title, Count(*) AS Count
FROM jud_Processos
WHERE
    Pressaus LIKE "0,%" OR
    Pressaus LIKE "%,0" OR
    Pressaus LIKE "%,0,%" OR
    Pressaus = "0"

UNION ALL

SELECT "Cirurgia" AS Title, Count(*) AS Count
FROM jud_Processos
WHERE
    Pressaus LIKE "1,%" OR
    Pressaus LIKE "%,1" OR
    Pressaus LIKE "%,1,%" OR
    Pressaus = "1"

UNION ALL

SELECT "Consulta Medica" AS Title, Count(*) AS Count
FROM jud_Processos
WHERE
    Pressaus LIKE "2,%" OR
    Pressaus LIKE "%,2" OR
    Pressaus LIKE "%,2,%" OR
    Pressaus = "2"