CODEWITHSUNDEEP
mysqlsql
Dr. Atul Tiwari
Dr. Atul Tiwari

Reputation: 1085

How to get number of duplicate rows in mysql

Mysql table "DemoTable" (id -> auto increment)

id   subject_id  subject_name  question  exams
1        3         sub3         ques1    e1,e4
2        3         sub3         ques2    e1
3        2         sub2         ques3    e1
4        2         sub2         ques4    e3,e5

What I want is following output with this where clause - where exams like '%e1%' 

subject_id  subject_name  number_of_questions
    3         sub3         2
    2         sub2         1

At present, I am using two separate queries -

To get list of subject names -

Select Distinct subject_id, subject_name from DemoTable where exams like '%e1%'

and

To get number of questions each subject has -

SELECT COUNT(*) as `num` FROM DemoTable where subject_id=$subject_id

But For my work, i need a single query doing both the work, as I mentioned above. How to do that?

Edit - 1. As mentioned by "juergen d" in his answer -

SELECT subject_id, subject_name, COUNT(distinct question) as questions_count 
FROM DemoTable 
where find_in_set('e1', exams) > 0
group by subject_id, subject_name

In this query find_in_set('e1', exams) > 0 works, but only as exams='e1' NOT exams like '%e1%' which is my case.

Any way to use like in find_in_set?

Thanks. Regards,

Upvotes: 0

Views: 92

Answers (1)

juergen d
juergen d

Reputation: 204924

SELECT subject_id, subject_name, COUNT(distinct question) as questions_count 
FROM DemoTable 
where find_in_set('e1', exams) > 0
group by subject_id, subject_name

Upvotes: 2

Related Questions