I have a Table call payments,

paymentid   stuid  stu_name   fullamount   pay_comp
1           1234   Jhon       2600         Feb
2           1212   Silva      1300         Jan
3           1234   Jhon       1300         March
4           1212   Silva      3900         April
5           3333   Perera     15600        Dec

Here a student can pay for the whole year,for several month or for a single month,Payments are done by 1300, For a example if "Silva" paid 1300 for the first time he will be paying for Jan. If he doing a another 1300 payment,Then it will be marked as for next month,Which is "Feb".If someone complete 15600 payment it will be saved as "Dec". The problem is that when calculating Total Amount for the Jan it must consider that 1300 from Dec payment.

What i want is to calculate total Amount for each month.The problem is "Pay_Com" column is insert records as above.It can be for the whole year or for a single month.Is there any way around this.

I tried different methods,can i use sum of full amount divided by 1300 to calculate full amount for each month.Can anyone suggest me a soultion or a sql Function. even tried IF and Sum with cases..

        WHEN round(sum(full_amount)/1300) = 1 THEN 1
        ELSE 0
    END AS 'Jan',
        WHEN round(sum(full_amount)/1300) = 2 THEN 2
        ELSE 0
    END AS 'Feb',
        WHEN round(sum(full_amount)/1300) = 3 THEN 3
        ELSE 0
    END AS 'March',

Can anyone suggest me a Solution. Thanks In Advance.

I am Looking for a Something Like.

Month   Full_PaymentsRecived
Jan     20000
Feb     3900
....    ....
Dec     2600
2017/04/26 05:25
  • php
  • mysql
  • sql
  • 点赞
  • 收藏
  • 回答