Count individual records in one column with multiple values ​​in another column

Question

I am sure this is a simple question, but I am having problems with its wording.

I need to calculate the total number of values ​​in one column based on different criteria in another column.

Example:

A CD

B ABC 

C AD

D A

Let's say:

A 3

B 1

C 2

D 2

Answer 1

First, you should not store lists of things in a line.

But sometimes one is stuck in this format. In your example, you seem to have a table with all possible values. If so, you can use join:

select e.col1, count(e2.col2)
from example e left join
     example e2
     on charindex(e.col1, e2.col2) > 0
group by e.col1;

Note. In this case, the rows containing the value are counted. If multiple values ​​appear on the same line, the query is a bit more complicated.

Answer 2

:

DECLARE @t TABLE ( c1 CHAR(1), c2 VARCHAR(5) )

INSERT  INTO @t
VALUES  ( 'A', 'CD' ),
        ( 'B', 'ABC' ),
        ( 'C', 'AD' ),
        ( 'D', 'A' )


SELECT  t.c1 ,
        SUM(count) AS count
FROM    @t t
        CROSS APPLY ( SELECT    LEN(c2) - LEN(REPLACE(c2, t.c1, '')) AS count
                      FROM      @t
                      WHERE     c2 LIKE '%' + t.c1 + '%'
                    ) ca
GROUP BY t.c1

Answer 3

, .

fielda fieldb
A      CD
B      ABC 
C      AD
D      A

SELECT a.fielda, (SELECT COUNT(b.fieldb)
FROM yourtable b
WHERE b.fieldb LIKE '%a.fielda%' AND b.fielda = a.fielda) AS counter
FROM yourtable a

Answer 4

LIKE

Data examples

with cte(a,b) as 
(
select 'A','CD'
union all select 'B','ABC' 
union all select'C','AD'
union all select'D','A'
)

Query

select a,(select count(*) from cte c2 where b like '%' + c1.a +'%')
from cte c1
group by a

Output

A   3
B   1
C   2
D   2

Answer 5

Use the counted subquery when counting. Use LIKEto find strings to search for strings to count.

select t1.col1, (select count(*) from tablename t2
                 where t2.col2 like '%' || t1.col1 ||'%')
from tablename t1

||- concatenation of ANSI SQL. Some products use concat()or +.

Answer 6

It sounds like you need to join yourself, but the trick would be to use pattern matching in the connection, not equi-join ...

create table x1(c1 char(1) primary key, c2 varchar(5) not null); 

select x1.c1, count(*) 
  from x1 x1 
  join x1 x2 on x2.c2 like '%' || x1.c1 || '%' 
  group by x1.c1 
  order by 1;