0

Let me illustrate this:

Student_ID Course
StudentID1 CourseA
StudentID2 CourseB
StudentID3 CourseA CourseB

There is an existing table that has data that looks like the one above (Not exactly Student & Course, this is more for illustration purpose) and my job is to do a count of how many students for each course. The table is very huge and I do not know how many and what are the courses are out there (Easily in thousands), so wonder if there is a way I can get a list of all these courses and their counts through SQL?

The current method that my team did is SELECT DISTINCT COURSE, COUNT(STUDENT_ID) FROM TABLE GROUP BY COURSE, but this does not work because it treats "CourseA CourseB" as its own.

There are some other columns in this table that I might need to do a SUM as well.

Appreciate any advice on this, thanks!

Fu Sing
  • 1
  • 4
    You can't sum a *string*. Storing multiple values in a string is a major antipattern and will only cause you problem after problem. Each value should be a row in a normalised table. – Stu Apr 30 '22 at 08:46
  • Agree with @Stu, and also, in order to explode your values to multiple rows, check [this out](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows). Once obtained the exploded table, you can `group by Course`, and then `count(Student_ID)` – crissal Apr 30 '22 at 08:48
  • Some questions: Which DB type are you using? Is there always a space between the courses? It will be possible to do such a select, but I agree that if possible you should go another way. – Jonas Metzler Apr 30 '22 at 09:03
  • Bad design to have multiple courses the way you do. Better to have table that has per-student/course that one student, multiple values in one column. Easier for joins later too. – DRapp Apr 30 '22 at 09:24

1 Answers1

0

you could use below to find number of students for each course:

select course, count(*) as no_of_students
from table 
group by course;
Deepak
  • 57
  • 1
  • 8