CODEWITHSUNDEEP
mysql
mongotop
mongotop

Reputation: 5774

select how many time a value is repeated inside multi-value columns

Consider this table 

student_name  grade
steve         a, b,d
mike          c,d,b
bob           a,d

I would like to write a query to pull the number of grade I do have out put

a    2
b    2
c    1
d    3

I've tried:

select s1.grade, count(s1.grade) from student s1, student s2
where s1.grade = s2.grade
group by s1.grade

How can this be done?

Upvotes: 0

Views: 71

Answers (2)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

Not pretty, but this is one reason why you don't want to violate first normal form and have multi-valued columns...

select 'a' as grade, count(*) as occurrences
from student
where grade like '%a%'

union all

select 'b' as grade, count(*) as occurrences
from student
where grade like '%b%'

union all

select 'c' as grade, count(*) as occurrences
from student
where grade like '%c%'

union all

select 'd' as grade, count(*) as occurrences
from student
where grade like '%d%'

See it in action here.

Alternately, if you have grades table like the one proposed by Chris K, you could do something like the following:

select g.grade, count(s.student_name) as occurances
from 
  grades g
  left join student s
    on concat(',', s.grade, ',') like concat('%,', g.grade, ',%')
group by g.grade

See it in action here.

Upvotes: 3

Chris K
Chris K

Reputation: 550

Or if you have a table (called grades) containing a list of possible grades:

grade
-----
a
b
c
d
e

Then the following statement would also work:

select g.grade as [Grade], (select count(1) from student where grade like '%'+g.grade+'%') as [Count] from grades g order by g.grade asc

This would maybe be more flexible in terms of adding other potential grades to the count.

But as said above... avoid normalisation at your peril...

Upvotes: 2

Related Questions