Hi Friends,

I have some forms and their tables like this:

#__cck_store_form_block_master

#__cck_store_form_cluster_master

#__cck_store_form_school_master

#__cck_store_form_student

#__cck_store_form_progress_entry

(block --> cluster --> school --> student --> progress[result])

I have to generate a report of Students who have got the marks in School exam, in report I need to generate some columns like:

Block, Cluster, School, Class, Total Students, Total Entry (of results), No. of Students and Percent of Students who achieved A grade(80% +), B grade (60-80%), C grade (40-60%), D grade (below 40%).

for this I wrote a SQL query like:

===================

SELECT
b.block_name,
cl.cluster_name,
sc.school_name,
s.class2,
s.Students,
E.Entry,
p1.A,
CONCAT(ROUND((p1.A / s.Students * 100), 2), '%') Percent_A,
p2.B,
CONCAT(ROUND((p2.B / s.Students * 100), 2), '%') Percent_B,
p3.C,
CONCAT(ROUND((p3.C / s.Students * 100), 2), '%') Percent_C,
p4.D,
CONCAT(ROUND((p4.D / s.Students * 100), 2), '%') Percent_D
FROM
#__cck_store_form_block_master b
JOIN
#__cck_store_form_cluster_master cl ON b.block_code = cl.cl_block_code
JOIN
#__cck_store_form_school_master sc ON sc.sc_cluster_code = cl.cluster_code
JOIN
(SELECT
school, class2, COUNT(*) Students
FROM
#__cck_store_form_student
GROUP BY school, class2) s ON s.school = sc.school_code

LEFT JOIN
(SELECT p_school , p_class,
COUNT(*) Entry
FROM
#__cck_store_form_progress_entry WHERE month = 'Final'
GROUP BY p_school , p_class) E ON E.p_school = s.school
AND E.p_class = s.class2

LEFT JOIN
(SELECT
p_school, p_class, month, COUNT(*) A
FROM
#__cck_store_form_progress_entry
WHERE
percent <= 100 AND percent >= 80
AND month = 'Final'
GROUP BY p_school , p_class) p1 ON p1.p_school = E.p_school
AND p1.p_class = E.p_class
LEFT JOIN
(SELECT
p_school, p_class, month, COUNT(*) B
FROM
#__cck_store_form_progress_entry
WHERE
percent >= 60 AND percent < 80
AND month = 'Final'
GROUP BY p_school , p_class) p2 ON p2.p_school = s.school
AND p2.p_class = s.class2
LEFT JOIN
(SELECT
p_school, p_class, COUNT(*) C
FROM
#__cck_store_form_progress_entry
WHERE
percent >= 40 AND percent < 60
AND month = 'Final'
GROUP BY p_school , p_class) p3 ON p3.p_school = s.school
AND p3.p_class = s.class2
LEFT JOIN
(SELECT
p_school, p_class, COUNT(*) D
FROM
#__cck_store_form_progress_entry
WHERE
percent >= 0 AND percent < 40
AND month = 'Final'
GROUP BY p_school , p_class) p4 ON p4.p_school = s.school
AND p4.p_class = s.class2
GROUP BY b.block_name , cl.cluster_name , sc.school_name , s.class2
ORDER BY b.block_name , cl.cluster_name , sc.school_name , s.class2 + 0 ASC

=================================================

which is working as required in mysql work bench, 

but when I try to do it in "Search Query" as usual (I have been using this plugin in my other projects also), it is showing "internal server error 500"

I need to generate graphs & charts along with the tabular report, so can you please give me any suggestions?

Best Regards,

Shubhaanshu