0

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,

1 Answer 1

2
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
Sign up to request clarification or add additional context in comments.

6 Comments

find_in_set('e1', exams) > 0 works only if exams = 'e1' doesn't work if exams like '%e1%' I even tried find_in_set('%e1%', exams) > 0 but no luck. How to get values for like too?
I am not sure whazt you try to do if that function does not work for you. Please add more example data and expected output
What i want to say is, the query you suggested works perfect only when value of string in find_in_set(string, column_name) is exactly equal, as you put it find_in_set('e1',exams). But in my case, column exams has multiple strings (see the table structure above, exams has e1,e4). So I need to use like, so that query can include the row which has value e1,e4 in column 'exams'. find_in_set('e1',exams) include rows which has only e1 in exams column and dont include which has multiple values e.g. e1,e4Hope I am clear this time.
find_in_set() does exactly what you want. Try it again. If it does not work then your example data is different from your real data.
i thought you went offline, so i asked new question with its exact behavior, please check this - Link of new question. sorry for the separate question.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.