I Need Help on Query Syntax

Opeyemi AbdulRasheed
Opeyemi AbdulRasheed used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
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 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

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial