Count occurrences in string via MySQL

Question

I want to count the occurrences of a string within the column "diagnosis".

What I am doing now is simply this - it gets my needed results

SELECT COUNT(*), diagnosis
FROM patients
WHERE diagnosis like "%OS1%"
UNION

SELECT COUNT(*), diagnosis
FROM patients
WHERE diagnosis like "%OS2%"

... and so on

Sometimes in my table the strings can occur twice (e.g. OS1, OS2), I want to count every single occurence of the strings.

I think it would be a pretty easy task in another language but I want to do it in pure SQL.

Put those values in a separate (temporary?) table and join with it! — jarlh
– jarlh, Commented Jun 4, 2018 at 7:50

jarlh · Accepted Answer · 2018-06-04 07:53:11Z

2

Put the OS1-OS6 and CH1-CH6 values in a diagn table. JOIN and GROUP BY:

SELECT COUNT(*), d.diagnosis
FROM patients p
RIGHT JOIN diagn d
   on p.diagnosis like concat('%', d.diagnosis, '%')
group by d.diagnosis

edited Jun 4, 2018 at 7:53

answered Jun 4, 2018 at 7:41

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vlam · Accepted Answer · 2018-06-04 07:57:32Z

0

SELECT sum(OS1) as OS1_total, sum(OS2) as OS2_total, sum(OS3) as OS3_total,
sum(OS4) as OS4_total, sum(OS5) as OS5_total, sum(OS6) as OS6_total, 
sum(CH1) as CH1_total, sum(CH2) as CH2_total, sum(CH3) as CH3_total,
sum(CH4) as CH4_total, sum(CH5) as CH5_total, sum(CH6) as CH6_total
FROM
(
SELECT (case when diagnosis like '%OS1%' then 1 else 0 end) as OS1,
(case when diagnosis like '%OS2%' then 1 else 0 end) as OS2,
(case when diagnosis like '%OS3%' then 1 else 0 end) as OS3,
(case when diagnosis like '%OS4%' then 1 else 0 end) as OS4,
(case when diagnosis like '%OS5%' then 1 else 0 end) as OS5,
(case when diagnosis like '%OS6%' then 1 else 0 end) as OS6,
(case when diagnosis like '%CH1%' then 1 else 0 end) as CH1,
(case when diagnosis like '%CH2%' then 1 else 0 end) as CH2,
(case when diagnosis like '%CH3%' then 1 else 0 end) as CH3,
(case when diagnosis like '%CH4%' then 1 else 0 end) as CH4,
(case when diagnosis like '%CH5%' then 1 else 0 end) as CH5,
(case when diagnosis like '%CH6%' then 1 else 0 end) as CH6
FROM patients
) as mytable

answered Jun 4, 2018 at 7:57

Vlam

1,7901 gold badge10 silver badges21 bronze badges

2 Comments

winternet Over a year ago

That one fixed it! Thanks a lot!

Vlam Over a year ago

If you find the answer useful, please mark it as useful by clicking the up arrow.

Tatyana · Accepted Answer · 2018-06-04 07:57:57Z

0

You can try this also:

SELECT COUNT(*), diagnosis
FROM patients
GROUP BY diagnosis;

answered Jun 4, 2018 at 7:57

Tatyana

236 bronze badges

Comments

Geo · Accepted Answer · 2018-06-04 08:24:30Z

0

Not sure if this works.

SELECT COUNT(*) 
FROM patients 
WHERE REGEXP_LIKE(diagnosis, "(OS[1-6]|CH[1-6])")

You may also check out link below if you're interested. Not sure if it's what you're looking for though:
https://dev.mysql.com/doc/refman/8.0/en/regexp.html

answered Jun 4, 2018 at 8:24

Geo

164 bronze badges

Collectives™ on Stack Overflow

Count occurrences in string via MySQL

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related