Mysql IN function

Question

class_table

+----+-------+--------------+
| id |teac_id| student_id   |
+----+-------+--------------+
| 1  | 1     | 1,2,3,4      |
+----+-------+--------------+

student_mark

+----+----------+--------+
| id |student_id| marks  |
+----+----------+--------+
| 1  | 1        | 12     |
+----+----------+--------+
| 2  | 2        | 80     |
+----+----------+--------+
| 3  | 3        | 20     |
+----+----------+--------+

I have these two tables and i want to calculate the total marks of student and my sql is:

SELECT SUM(`marks`) 
FROM `student_mark`
WHERE `student_id` IN 
  (SELECT `student_id` FROM `class_table` WHERE `teac_id` = '1')

But this will return null, please help!!

DB fiddle

Never, ever store data as comma separated items. It will only cause you lots of trouble. — jarlh, Oct 09 '18 at 06:27

Madhur Bhaiya · Accepted Answer · 2018-10-09T04:34:47.690

Firstly, you should never store comma separated data in your column. You should really normalize your data. So basically, you could have a many-to-many table mapping teacher_to_student, which will have teac_id and student_id columns.
In this particular case, you can utilize Find_in_set() function.

From your current query, it seems that you are trying to getting total marks for a teacher (summing up marks of all his/her students).

Try:

SELECT SUM(sm.`marks`) 
FROM `student_mark` AS sm
JOIN `class_table` AS ct 
  ON FIND_IN_SET(sm.`student_id`, ct.`student_id`) > 0
WHERE ct.`teac_id` = '1'

In case, you want to get total marks per student, you would need to add a Group By. The query would look like:

SELECT sm.`student_id`, 
       SUM(sm.`marks`) 
FROM `student_mark` AS sm
JOIN `class_table` AS ct 
  ON FIND_IN_SET(sm.`student_id`, ct.`student_id`) > 0
WHERE ct.`teac_id` = '1' 
GROUP BY sm.`student_id`

score 0 · Answer 2 · answered Oct 09 '18 at 05:23

Just in case you want to know why, The reason it returned null is because the subquery returned as '1,2,3,4' as a whole. What you need is to make it returned 1,2,3,4 separately.

What your query returned

SELECT SUM(`marks`) 
FROM `student_mark`
WHERE `student_id` IN ('1,2,3,4')

What you expect is

SELECT SUM(`marks`) 
FROM `student_mark`
WHERE `student_id` IN (1,2,3,4)

The best way is it normalize as @madhur said. In your case you need to make the teacher and student as one to many link

+----+-------+--------------+
| id |teac_id| student_id   |
+----+-------+--------------+
| 1  | 1     | 1            |
+----+-------+--------------+
| 2  | 1     | 2            |
+----+-------+--------------+
| 3  | 1     | 3            |
+----+-------+--------------+
| 4  | 1     | 4            |
+----+-------+--------------+

Michał Turczyn · Answer 3 · 2018-10-09T06:03:08.183

If you want to filter your table based on a comma separated list with ID, my approach is to append extra commas at the beginning and at the end of a list as well as at the beginning and at the end of an ID, eg. 1 becomes ,1, and list would become ,1,2,3,4,. The reason for that is to avoid ambigious matches like 1 matches 21 or 12 in a list.

Also, EXISTS is well-suited in that situation, which together with INSTR function should work:

SELECT SUM(`marks`) 
FROM `student_mark` sm
WHERE EXISTS(SELECT 1 FROM `class_table` 
             WHERE `teac_id` = '1' AND
             INSTR(CONCAT(',', student_id, ','), CONCAT(',', sm.student_id, ',')) > 0)

Demo

BUT you shouldn't store related IDs in one cell as comma separated list - it should be foreign key column to form proper relation. Joins would become trivial then.

Mysql IN function

3 Answers3