Struggling with MySQL subquery -

i have 2 tables, image , gradereason. each image awarded grade it's quality , user can select 4 different reasons (reasonid_1, reasonid_2, reasonid_3, reasonid_4) using selecting reasonid. breakdown of reason stored in gradereason table.

image  imageid   auditid  reasonid_1  reasonid_2  reasonid_3  reasonid_4 -------   -------  ----------  ----------  ----------  ---------- 1         123      1           13          7           3 2         124      8           13          8           6  4         125      3           2           5           6 5         125      7           4           2           3  gradereason  reasonid   category   name --------   --------   ---- 1          exposure   overexposed 2          exposure   underexposed 3          patient    patient moved 4          equipment  sensor big 5          equipment  sensor small 

what query return number of times each reasonid has been used in audit , name of gradereason was

e.g.

audit 125 -

reasonid 3 used twice - name 'patient moved',

reasonid 2 used twice - name 'underexposed'.

i'll honest , have struggled days , can't think begin.

this harder because data not normalized. following approach first normalizes data, join , aggregation:

select ir.auditid, gr.reasonid, gr.category, gr.name, count(*) cnt (select i.imageid, i.auditid,              (case when n.n = 1 reasonid_1                    when n.n = 2 reasonid_2                    when n.n = 3 reasonid_3                    when n.n = 4 reasonid_4               end) reasonid       image cross join            (select 1 n union select 2 union select 3 union select 4            ) n       ) ir join       gradereason gr       on ir.reasonid = gr.reasonid group ir.auditid, gr.reasonid, gr.category, gr.name order cnt desc;