Need suggestions on a MS SQL query:
table: students
id name class
-- ---- -----
1 John Math, Biology
2 Jane English
3 Mark Math
table: class
id name
-- ----
1 Math
2 English
3 Biology
4 History
Desired output
name count
---- -----
Math 2
English 1
Biology 1
History 0
This is for MySQL.
SELECT a.name, COUNT(b.id) as `totalCount`
FROM class a
LEFT JOIN students b
ON FIND_IN_SET(a.name, b.class) > 0
GROUP BY a.name
for best performance, normalize the tables such by creating 3 tables,
students
Class
StudentClass
This is a many-to-many relationship since students may have many classes while classes have also have many students.
And the query should look like this,
SELECT a.Name, COUNT(b.ClassID) totalCount
FROM Class a
LEFT JOIN StudentClass b
ON a.id = b.ClassID
GROUP BY a.Name
Here is the answer for SQL Server:
SELECT c.name, COUNT(b.id) as `totalCount`
FROM class c LEFT JOIN
students s
ON ','+s.class+',' like '%,'+a.name+',%'
GROUP BY c.name
Actually, that doesn't work well with the spaces that you have in the class names. Try this on instead:
ON ','+replace(s.class, ' ', '')+',' like '%,'+replace(a.name, ' ', '')+',%'
select
class.name as name,
count(*) as count
from students, class
where students.class like '%'+class.name+'%'
classand in it you need to parse the comma list to get the class names? I notice in your exampleSciencedoesn't appear anywhere except in theStudentstable. Is that a bug or do you really not what that result?