MySQL add columns from query results

I'd like to add/create three columns from table column based on this particular column results. This is the records I've in a tblexammarks:

SELECT exm.ID, 
       exm.Admission_No, 
       exm.Subject AS 'SID', 
       sb.Name AS 'Subject Name', 
       exm.Term, 
       exm.Test, 
       exm.Marks 
FROM tblexammarks exm 
INNER JOIN tblsubjects sb ON exm.Subject=sb.ID 
ORDER BY exm.ID ASC;

Output:

ID	Admission_No	SID	Subject Name	Term	Test	Marks
1	KBA-2022-003	6	Science	Term One	Test One	94
2	KBA-2022-003	6	Science	Term One	Test Two	88
3	KBA-2022-003	6	Science	Term One	Test Three	78

Here's what I'm trying to achieve:

Admission_No	SID	Subject Name	Term	Test One	Test Two	Test Three	Total Marks
KBA-2022-003	6	Science	Term One	94	88	78	260

What I've tried and the output:

SELECT exm.ID, 
       exm.Admission_No, 
       exm.Subject AS 'SID', 
       sb.Name AS 'Subject Name', 
       exm.Term, 
       if(Test='Test One', marks, 0) AS 'Test One', 
       if(Test='Test Two', marks, 0) AS 'Test Two', 
       if(Test='Test Three', marks, 0) AS 'Test Three'
FROM tblexammarks exm 
INNER JOIN tblsubjects sb ON exm.Subject=sb.ID;

ID	Admission_No	SID	Subject Name	Term	Test One	Test Two	Test Three
1	KBA-2022-003	6	Science	Term One	94	0	0
2	KBA-2022-003	6	Science	Term One	0	88	0
3	KBA-2022-003	6	Science	Term One	0	0	78

I've tried using MySQL IF Function but I'm lost. Can someone help.

by Charles in StackOverflow on 2022-06-04.

Answer

You can try using a conditional statement IF and extract the single values for each of the three Test fields, then use the MAX aggregation function to remove the null values and the SUM to get a total for the Marks field, then aggregate over the rest of the selected fields using the GROUP BY clause.

SELECT exm.Admission_No, 
       exm.Subject                                          AS SID, 
       sb.Name                                              AS Subject_Name, 
       exm.Term, 
       MAX(IF(exm.Test = 'Test One'  , 'Test One'  , NULL)) AS Test_One, 
       MAX(IF(exm.Test = 'Test Two'  , 'Test Two'  , NULL)) AS Test_Two, 
       MAX(IF(exm.Test = 'Test Three', 'Test Three', NULL)) AS Test_Three, 
       SUM(exm.Marks)                                       AS Marks
FROM       tblexammarks exm 
INNER JOIN tblsubjects sb 
        ON exm.Subject = sb.ID 
GROUP BY exm.Admission_No, 
         exm.Subject AS 'SID', 
         sb.Name AS 'Subject Name', 
         exm.Term
ORDER BY exm.ID ASC;

If you can provide sample data from the two tables, I can provide a fully tested solution.

by lemon on 2022-06-04.