I Need Help on Query Syntax

Hello Experts!

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

Open in new window


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

Open in new window


Is that doable, please?
Opeyemi AbdulRasheedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"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.

Chris StanyonWebDevCommented:
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
0
pcelbaCommented:
You may use following query:
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

Open in new window

The syntax is rather MS SQL Server but it should work in MySQL which I expect is your case.
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

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
Opeyemi AbdulRasheedAuthor Commented:
Thank you so much. It works.
I only had to change this:

[1st_TERM], 
[2nd_TERM], 
[3rd_TERM],

Open in new window


To:
`1st_TERM`, 
`2nd_TERM`, 
`3rd_TERM`,

Open in new window

0
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
Query Syntax

From novice to tech pro — start learning today.