Find the total amount of class fees based on a class of students studying for specific years using mysql or php

Question

I look forward to the total payments made by each class and to students in the class in which he or she has ever participated, depending on the academic year.

Now Student Class Tablethere are two students below , a student with Student ID=0001was enrolled in two classes in two different years Class ID = 1, Year=2013and Class ID=2, Year=2014.

However, the student with Student ID=0002only enrolled in one class, and he left school. Another student was enrolled in class Class ID = 1inYear=2014

    Student Class Table
    |   Student ID  |   Class ID    |   Year    |
    |      0001     |      1        |   2013    |
    |      0002     |      1        |   2013    |
    |      0003     |      1        |   2014    |
    |      0001     |      2        |   2014    |

Below is the amount assigned to each student for each class for a specific period.

    Designated Fees
    |   Class ID    |   Amount  |   Year    |
    |    1          |    100    |   2013    |
    |    1          |    120    |   2014    |
    |    2          |    210    |   2014    |

, "" ( , 2014 ), , ( , 2013 - ) ( 2014 - ).

, , , / .

        SELECT 
              a.`Student ID`,
              a.`Class ID`,
              b.`Amount`,
              a.`Year

        FROM 
             `Student Class Table` a
        JOIN 
             `Designated Fees` b
        ON
                a.`Class ID`=b.`Class ID`
                    and                                         
                 a.Year=b.Year


    QUERY RESULTS

    |   Student ID  |   Class ID    |   Amount  |   Year    |
    |      0001     |      1        |     100   |   2013    |
    |      0002     |      1        |     100   |   2013    |
    |      0003     |      1        |     120   |   2014    |
    |      0001     |      2        |     210   |   2014    |

, , , 2014 - .

    |   Class ID    |   Total Fees  |   Year    |   
    |       1       |       120     |   2014    |
    |       2       |       410     |   2014    |

Total Fees Class ID=1 120, 2013 2014 , 2014 - 120, Total Fees Class ID=2 410, Class ID = 1 2013 , 200, 2014 , 210.

, , group by , , , . , mysql php, .

Answer 1

, , . №, .

, Class ID= 1 Year=2013 No . Student Class Table , .

Student Class Table
|   Student ID  |   Class ID    |   Year    |  BatchNo    |
|      0001     |      1        |   2013    |     1       |
|      0002     |      1        |   2013    |     1       |
|      0003     |      1        |   2014    |     2       |
|      0001     |      2        |   2014    |     1       |

, group by BatchNo :

  SELECT 
          a.`Student ID`,
          a.`Class ID`,
          sum(b.`Amount`),
          a.`Year

    FROM 
         `Student Class Table` a
    JOIN 
         `Designated Fees` b
    ON
            a.`Class ID`=b.`Class ID`
                and                                         
             a.Year=b.Year
  GROUP BY batchNo

|   Class ID    |   Total Fees  |   Year    |   
|       1       |       120     |   2014    |
|       2       |       410     |   2014    |

, , .