dongmu5920
dongmu5920
2017-04-26 05:25
浏览 171
已采纳

mysql函数用于除以列的和值

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..

SELECT 
    CASE
        WHEN round(sum(full_amount)/1300) = 1 THEN 1
        ELSE 0
    END AS 'Jan',
     CASE
        WHEN round(sum(full_amount)/1300) = 2 THEN 2
        ELSE 0
    END AS 'Feb',
     CASE
        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
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douyinlai2169
    douyinlai2169 2017-04-27 09:12
    已采纳

    You know that every stuid is due 1300 every month, you can calculate how much has been paid by every stuid. From this you can apportion forwards from month one decrementing the paid amount as you go.

    SELECT Y.MM,
                SUM(Y.DUEAMT) PAYMENTSEXPECTED,
                SUM(Y.PAID) PAYMENTSRECEIVED, 
                (SUM(Y.PAID) / SUM(Y.DUEAMT)) * 100 RATIO_PAID_DUE,
                SUM(CASE WHEN Y.PAID > 0 THEN 1 ELSE 0 END) NUMBER_OF_PAYMENTS_RECEIVED,
                SUM(CASE WHEN Y.PAID = 0 THEN 1 ELSE 0 END) NUMBER_OF_PAYMENTS_MISSED,
                SUM(CASE WHEN Y.PAID < Y.DUEAMT AND Y.PAID > 0 THEN 1 ELSE 0 END) NUMBER_OF_UNDERPAYMENTS
    FROM
    (
    SELECT X.STUID,X.MM,
             X.DUEAMT,
             #IF(X.STUID <> @P, @TOTALPAID:=(SELECT SUM(FULLAMOUNT) FROM T WHERE T.STUID = X.STUID),0) TOTALPAID,
             IF(X.STUID <> @P, @FULLAMOUNT:=(SELECT SUM(FULLAMOUNT) FROM T WHERE T.STUID = X.STUID) 
             #- X.DUEAMT
             ,
             @FULLAMOUNT:=@FULLAMOUNT - X.DUEAMT) EXHAUST,
             CASE WHEN @FULLAMOUNT >= X.DUEAMT THEN X.DUEAMT
                   WHEN @FULLAMOUNT < 0 THEN 0
             ELSE @FULLAMOUNT 
             END AS PAID,
    
             @P:=X.STUID 
     FROM 
    (
    SELECT DISTINCT T.STUID , 1300 AS DUEAMT, S.MM 
    FROM T,
    (
    SELECT 1 AS MM UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
    SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION
    SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ) S
    ) X , (SELECT @TOTALPAID:=0,@FULLAMOUNT:=0,@P:=0) FA
    ORDER BY X.STUID,X.MM
    ) Y
    GROUP BY Y.MM
    

    The sub query S generates a due amount for each month for each student The Sub query X then works out and amount paid for each month for each student The sub query Y then aggregates to get a result (I have added some addition analysis not required in your question).

    So given

    MariaDB [sandbox]> select * from t;
    +-----------+-------+----------+------------+----------+
    | paymentid | stuid | stu_name | fullamount | pay_comp |
    +-----------+-------+----------+------------+----------+
    |         1 |  1234 | Jhon     |       2600 | Feb      |
    |         2 |  1212 | Silva    |       1000 | Jan      |
    |         3 |  1234 | Jhon     |       1300 | Mar      |
    |         4 |  1212 | Silva    |       3900 | Apr      |
    |         5 |  3333 | Perera   |      15600 | Dec      |
    +-----------+-------+----------+------------+----------+
    

    5 rows in set (0.02 sec)

    Result

    MM | PAYMENTSEXPECTED | PAYMENTSRECEIVED | RATIO_PAID_DUE    | NUMBER_OF_PAYMENTS_RECEIVED | NUMBER_OF_PAYMENTS_MISSED | NUMBER_OF_UNDERPAYMENTS |
    +----+------------------+------------------+-------------------+-----------------------------+---------------------------+-------------------------+
    |  1 |             3900 |             3900 |               100 |                           3 |                         0 |                       0 |
    |  2 |             3900 |             3900 |               100 |                           3 |                         0 |                       0 |
    |  3 |             3900 |             3900 |               100 |                           3 |                         0 |                       0 |
    |  4 |             3900 |             2300 | 58.97435897435898 |                           2 |                         1 |                       1 |
    |  5 |             3900 |             1300 | 33.33333333333333 |                           1 |                         2 |                       0 |
    |  6 |             3900 |             1300 | 33.33333333333333 |                           1 |                         2 |                       0 |
    |  7 |             3900 |             1300 | 33.33333333333333 |                           1 |                         2 |                       0 |
    |  8 |             3900 |             1300 | 33.33333333333333 |                           1 |                         2 |                       0 |
    |  9 |             3900 |             1300 | 33.33333333333333 |                           1 |                         2 |                       0 |
    | 10 |             3900 |             1300 | 33.33333333333333 |                           1 |                         2 |                       0 |
    | 11 |             3900 |             1300 | 33.33333333333333 |                           1 |                         2 |                       0 |
    | 12 |             3900 |             1300 | 33.33333333333333 |                           1 |                         2 |                       0 |
    +----+------------------+------------------+-------------------+-----------------------------+---------------------------+-------------------------+
    12 rows in set (0.04 sec)
    
    点赞 评论

相关推荐