SQL to count records that have a repeating value field

2

Personal I need a Query that does a count of values that I have in the bank

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

I wanted your help, I never moved with a query, I need a Query that tells me how many numbers I have in the database of this variable, how many numbers I have in the database of this variable, Being that when you have more than one option selected in the bank it becomes 1, 2, 9, 10.

Can anyone help me? Use SQLServer


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

  SELECT DISTINCT Pressaus ,COUNT(*) AS quantidade
  FROM jud_Processos
 GROUP BY Pressaus
 ORDER BY quantidade DESC
    
asked by anonymous 25.07.2017 / 17:24

1 answer

0

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

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

Considering that the alternatives are the numbers of 0 a 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 each CASE WHEN creates a column for each option and in that column the number 1 is inserted if the line has the option in the Pressaus field. Since the option can be in the beginning, in the middle, in the end or exactly the field, we need 4 clauses WHEN one for each possibility.

Then the function SUM() adds up how many times each alternative has appeared.

Example in sqlfiddle: link

    
25.07.2017 / 19:28