[SOLUTION] I Need Help on Query Syntax

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?

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

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.

LVL 54

Chris StanyonWebDevCommented: 2018-10-13

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

LVL 47

pcelbaCommented: 2018-10-13

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

LVL 1

Opeyemi AbdulRasheedAuthor Commented: 2018-10-13

Thank you so much. It works.
I only had to change this:

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

To:

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

I Need Help on Query Syntax

Who is Participating?