MySQL add columns from query results-CodePudding

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.

CodePudding user response：

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.