CODEWITHSUNDEEP
mysqlregexcountsum
CG3
CG3

Reputation: 47

How to count the occurrences of a string in mysql

I have a database view (viewAll) containing these columns:

Locus, varchar(10)
MismatchDesc, varchar(200)

This table could look like:

Locus MismatchDesc
HLA-A 435delA;553insG
HLA-B 665subA;776subA;998delG 
HLA-B 776subA;998delG
HLA-B 998delG
HLA-A 553insG

I need to produce a view with additional column MismatchCount So that it looks like:

Locus MismatchDesc MismatchCount
HLA-A 435delA;553insG 1
HLA-B 665subA;776subA;998delG 1 
HLA-B 776subA;998delG 1
HLA-B 998delG 3
HLA-A 553insG 2

Currently I have:

CREATE VIEW mismatchCount AS SELECT Locus, MismatchDesc, SUM(IF('%'+MismatchDesc+'%',1,0)) AS MismatchCount FROM viewAll GROUP BY MismatchDescGDNA ORDER BY mismatchCount ASC;

I had hoped it would produce my required output using sneaky wildcards and the sum function, but it is not, it is simply producing an output in a more complicated way than had I just used 'count'.

So this currently produces output:

Locus MismatchDesc MismatchCount
HLA-A 435delA;553insG 1 
HLA-B 665subA;776subA;998delG 1 
HLA-B 776subA;998delG 1
HLA-B 998delG 1
HLA-A 553insG 1

Somewhere out there, there must be a way to count the occurrence of each string, and each instance matching a string?

I cannot separate the strings, which are semi colon separated because I need to count each unique string instance as a whole also. I hope this makes sense.

EDIT

I am not limited to my way of performing the query, if there is a more effective and quicker way, please share, because this is to process a huge amount of data, and therefore likely very slow.

Upvotes: 0

Views: 107

Answers (1)

user5992977
user5992977

Reputation:

You can use a correlated query to get the matching count for each Locus like this:

CREATE VIEW mismatchCount AS
SELECT s.Locus,
       s.MismatchDesc,
       (select count(*) from YourTable t
       WHERE t.MismatchDesc like concat('%',s.mismatchDesc,'%') as MismatchCount
FROM YourTable s

Or with a join:

CREATE VIEW mismatchCount AS
SELECT s.Locus,
       s.MismatchDesc,
       count(case when t.MismatchDesc like concat('%',s.mismatchDesc,'%') then 1 end) as MismatchCount
FROM YourTable s
INNER JOIN YourTable t ON(1=1)
GROUP BY s.locus,s.mismatchDesc

Upvotes: 1

Related Questions