SQL to count records that have a field with repeated value

Question

Staff I need a Query that does a value count I have in the bank

On my system, I have a multiselect that can select more than one option.

I wanted a help from you, I never touched with query, I need a Query that counts how many numbers 1 I have in the bank of this variable, how many numbers 2 I have in the bank of this variable, When you have more than one option selected in the bank it is like this 1, 2 , 9, 10.

Could anyone help me? I use Sqlserver

I did this query, only it only counts values that have no comma

  SELECT DISTINCT Pressaus ,COUNT(*) AS quantidade
  FROM jud_Processos
 GROUP BY Pressaus
 ORDER BY quantidade DESC

Answer 1

The way the data is stored in the database makes this kind of query very difficult because the data is not normalized.

The code below performs your query, but it is necessary to know beforehand how many options can be chosen, because Voce will need to create a CASE WHEN for each of them.

whereas the alternatives are the numbers of 0 to 12

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 "Total Opcao0",
  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 "Total Opcao1",
  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 "Total Opcao2",
  .
  .  -- CASE WHEN para cada opcao...
  .
  SUM(CASE 
    WHEN Pressaus LIKE "12,%" THEN 1
    WHEN Pressaus LIKE "%,12" THEN 1
    WHEN Pressaus LIKE "%,12,%" THEN 1
    WHEN Pressaus = "12" THEN 1
    ELSE 0
  END) AS "Total Opcao12"
FROM jud_Processos;

Basically every CASE WHEN creates a column for each option and in that column is inserted the number 1 if the line has the option in the field Pressaus. As the option may be at the beginning, in the middle, at the end or be exactly the field we need 4 clauses WHEN one for each possibility.

Then the function SUM() sum how many times each alternative appeared.

Example in sqlfiddle: http://sqlfiddle.com/#! 9/e14763/23