I Need Help on Query Syntax
Hello Experts!
I have this table - tbl_enroll:
Now the challenge: I want a SELECT QUERY to give something like:
Is that doable, please?
I have this table - tbl_enroll:
ENROLL_ID STU_ID SUBJECT_CODE CLASS_NAME SESSION TERM MARKS
1 001 ENG SS1A 2018 1st 40
2 001 MATHS SS1A 2018 1st 40
3 002 ENG SS1A 2018 1st 50
4 002 MATHS SS1A 2018 1st 50
5 001 ENG SS1A 2018 2nd 45
6 001 MATHS SS1A 2018 2nd 50
7 002 ENG SS1A 2018 2nd 55
8 002 MATHS SS1A 2018 2nd 55
9 001 ENG SS1A 2018 3rd 60
10 001 MATHS SS1A 2018 3rd 70
11 002 ENG SS1A 2018 3rd 65
12 002 MATHS SS1A 2018 3rd 50
Now the challenge: I want a SELECT QUERY to give something like:
ENROLL_ID STU_ID SUBJECT_CODE CLASS_NAME SESSION 1st_TERM 2nd_Term 3rd_Term Total Average
1 001 ENG SS1A 2018 40 45 60 145 48.3
2 001 MATHS SS1A 2018 40 50 70 160 53.3
3 002 ENG SS1A 2018 50 55 65 170 56.7
4 002 MATHS SS1A 2018 50 55 50 155 51.7
Is that doable, please?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What you're looking at is a Pivot Table. MySQL can't do native crosstab queries, so it would probably need quite a bit of messing about.
You've tagged this post as PHP - I would suggest you build your data model using that instead. It'll probably be a lot easier than trying to do it in mysql
You've tagged this post as PHP - I would suggest you build your data model using that instead. It'll probably be a lot easier than trying to do it in mysql
You may use following query:
You should clarify the ENROLL_ID on output. It cannot be simply copied from the tbl_enroll so what it should be? Just output rows numbering? Or the minimum value from the ENROLL_ID for given group?
This is one possible solution for ENROLL_ID on output:
SELECT STU_ID, SUBJECT_CODE, CLASS_NAME, SESSION,
SUM(CASE WHEN TERM = '1st' THEN MARKS ELSE NULL END) as [1st_TERM],
SUM(CASE WHEN TERM = '2nd' THEN MARKS ELSE NULL END) as [2nd_TERM],
SUM(CASE WHEN TERM = '3rd' THEN MARKS ELSE NULL END) as [3rd_TERM],
SUM(MARKS) as Total,
AVG(MARKS) as Average
FROM tbl_enroll
GROUP BY STU_ID, SUBJECT_CODE, CLASS_NAME, SESSION
ORDER BY STU_ID, SUBJECT_CODE, CLASS_NAME, SESSION
You should clarify the ENROLL_ID on output. It cannot be simply copied from the tbl_enroll so what it should be? Just output rows numbering? Or the minimum value from the ENROLL_ID for given group?
This is one possible solution for ENROLL_ID on output:
SELECT MIN(ENROLL_ID) as ENROLL_ID,
STU_ID, SUBJECT_CODE, CLASS_NAME, SESSION,
SUM(CASE WHEN TERM = '1st' THEN MARKS ELSE NULL END) as [1st_TERM],
SUM(CASE WHEN TERM = '2nd' THEN MARKS ELSE NULL END) as [2nd_TERM],
SUM(CASE WHEN TERM = '3rd' THEN MARKS ELSE NULL END) as [3rd_TERM],
SUM(MARKS) as Total,
AVG(MARKS) as Average
FROM tbl_enroll
GROUP BY STU_ID, SUBJECT_CODE, CLASS_NAME, SESSION
ORDER BY STU_ID, SUBJECT_CODE, CLASS_NAME, SESSION
Premium Content
You need an Expert Office subscription to comment.Start Free Trial