I Need Help on Query Syntax
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?
Who is Participating?
Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
Experts Exchange Solution brought to you by
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
I only had to change this:
[1st_TERM],
[2nd_TERM],
[3rd_TERM],
To:
`1st_TERM`,
`2nd_TERM`,
`3rd_TERM`,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
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