There are 2 tables (subj, rait). It is necessary to derive the medial value in subjects, that is
subject_id, subject name, medial value for the given subject.

How to bring the medial in subjects without the name of the object - I understood, but I don’t understand how to shove the name of the object there, at least kill me.

SELECT `subject_id`,(MAX(`value`)+MIN(`value`))/2 FROM (SELECT cs.`subject_id`,`value` FROM (SELECT subject_id,value, ( SELECT COUNT(1) FROM rating WHERE `value`<o.`value` AND `subject_id`=o.`subject_id` ) as ls , ( SELECT COUNT(1) FROM rating WHERE `value`<=o.`value` AND `subject_id`=o.`subject_id` ) as lse FROM rating o ) cs JOIN (SELECT `subject_id`,COUNT(1)*.5 as cn FROM rating GROUP BY `subject_id` ) cc ON cs.`subject_id`=cc.`subject_id` WHERE cn between ls and lse ) AS medians GROUP BY `subject_id`

Subjects

Rating

    2 answers 2

    If you display subj_id in the resulting query, then you just have to join it with the table of names.

     select subj.id, subj.name, T1.val from ( SELECT `subject_id`,(MAX(`value`)+MIN(`value`))/2 as val FROM ... ) as T1 LEFT JOIN subj ON subj.id=T1.subj_id
    • As an option. Only here the order of the columns is important: id, name, val - 1Page

    Having broken my head a little (everything turned out to be much simpler - I needed a smoke break and a tea break) - it was finished until the next option, which gives everything in the right form:

     SELECT subject_id, name, (MAX(`value`)+MIN(`value`))/2 FROM ( SELECT cs.`subject_id`,cs.`name`,`value` FROM (SELECT o.subject_id, subject.name, o.value, ( SELECT COUNT(1) FROM rating WHERE `value`<o.`value` AND `subject_id`=o.`subject_id` ) as ls , ( SELECT COUNT(1) FROM rating WHERE `value`<=o.`value` AND `subject_id`=o.`subject_id` ) as lse FROM rating o, subject ) cs JOIN (SELECT `subject_id`,COUNT(1)*.5 as cn FROM rating GROUP BY `subject_id` ) cc ON cs.`subject_id`=cc.`subject_id` WHERE cn between ls and lse ) as finaldata GROUP BY subject_id