103 Posts
shubhaanshu
6 years ago
2
Topic

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

Get a VIP membership
248 Posts
Giuse
6 years ago
1
Level 1

One of the thing you need to check is the length of your query, since it is stored in a Seblod table column so just verify if it fits the column size (if not, just alter table enlarging the dimension). With long queries, the SQL can be just truncated and provides unpredictable behavior.

Hope it helps.

4229 Posts
Kadministrator
6 years ago
0
Level 2

If your server is using protection system like mod_secueitry, than internal server error masks real errors, in your case most probably a query error. Open this field and see if your query was saved correctly. If no, you will need to increase size of   options2 column (which is currently set to text, set it to mediumtext) in cck_core_fields table. If it was saved properly than that is not the problem and you will need to out what real error is, e.g. by making a copy of oyur site locally to see the real error or ask your hoster to give you the log.

Get a VIP membership