calculation for cash and card payment report

[Yohanne]

i have foreach loop for payment list of a client if they fully paid or have a balance, my problem is client has two receipt like payment in cash and payment in card. he/she paid by different date but don't mind the date.. here is my table

dbtable scenario 

uid | name | payment_type | cost     | payment  |
1    |  kim    |   card                |    100   |   100    |
2    |  lee     |   cash               |    95      |   95     |
3    |  kent   |   cash              |   100      |   50     |   
3    |  kent   |   card               |   100      |   50     |
4    |  iya      |   cash              |    80       |   40     |
4    |  iya      |  card                |   80        |   20     |

its should be the output

uid | name   | cash   |  card  | cost   | balance |
1    | kim       |  0        |   100  |  100   |  0            |
2    | lee        |  95      |   0      |  95      |  0            |
3    | kent      |  50      |   0     |  100    |  50          |<-this part 100-50 = 50 balance   
3    | kent      |  0       |   50    |  100   |  0             |<-balance 50-50 = 0 balnce
4    | iya        |  40      |   0      |  80     |  40         |<-this part 80-40 = 40 balance 
4    | iya        |  0        |   20    |  80     |  20         |<-balance 40-20 = 20 balnce

need your assistance newbe here for this scenario but i try to resolve but i got failed. i did not get the correct output.. 

Edited March 6, 2020 by Yohanne

[requinix]

Well, the table structure isn't really good to work with. That's your real problem. Because as far as I can read that table, Kent has a total cost 200 and total payment 100, and Iya has total cost 160 and total payment 60.

You need a concept of separate orders and payment. One table stores the 100 cost for Kent, then another table stores the 50 cash payment and 50 card payment.

[Barand]

I totally agree with @requinix regarding the two  tables.

However, if you are willing to compromise over the output, you could do something like this

SELECT uid
     , name
     , SUM(CASE payment_type
            WHEN 'cash' THEN payment
            ELSE 0
            END) as cash
     , SUM(CASE payment_type
            WHEN 'card' THEN payment
            ELSE 0
            END) as card
     , cost
     , cost-SUM(payment) as balance
FROM payment
GROUP BY uid

+------+------+------+------+------+---------+
| uid  | name | cash | card | cost | balance |
+------+------+------+------+------+---------+
|    1 | kim  |    0 |  100 |  100 |       0 |
|    2 | lee  |   95 |    0 |   95 |       0 |
|    3 | kent |   50 |   50 |  100 |       0 |
|    4 | iya  |   40 |   20 |   80 |      20 |
+------+------+------+------+------+---------+

If you really need every transaction listed, the SQL becomes quite complex involving user variables and subqueries. It would be much easier to do in the PHP as you output each row.

[EDIT] ...

For the sake of completeness

SELECT uid
     , name
     , cash
     , card
     , cost
     , cost-total as balance
FROM (
        SELECT 
               name
             , CASE payment_type
                    WHEN 'cash' THEN payment
                    ELSE 0
                    END as cash
             , CASE payment_type
                    WHEN 'card' THEN payment
                    ELSE 0
                    END as card
             , cost
             , @tot := CASE @previd
                            WHEN uid THEN @tot + payment
                            ELSE payment
                            END as total
             , @previd := uid as uid
        FROM (
                SELECT * 
                FROM payment 
                ORDER BY uid
             ) sorted
             JOIN (SELECT @previd:=0, @tot:=0) initialize
) recs;

+------+------+------+------+------+---------+
| uid  | name | cash | card | cost | balance |
+------+------+------+------+------+---------+
|    1 | kim  |    0 |  100 |  100 |       0 |
|    2 | lee  |   95 |    0 |   95 |       0 |
|    3 | kent |   50 |    0 |  100 |      50 |
|    3 | kent |    0 |   50 |  100 |       0 |
|    4 | iya  |   40 |    0 |   80 |      40 |
|    4 | iya  |    0 |   20 |   80 |      20 |
+------+------+------+------+------+---------+

 

Edited March 6, 2020 by Barand

[Barand]

An alternative to the 2-table option is to treat costs as transactions, just like payments (cost amounts +ve, payment amounts -ve in this example)...

DATA

TABLE: payment
+------+------+------------+--------------+---------+
| uid  | name | trans_date | payment_type | payment |
+------+------+------------+--------------+---------+
|    1 | kim  | 2020-03-01 | cost         |     100 |
|    1 | kim  | 2020-03-02 | card         |    -100 |
|    2 | lee  | 2020-03-01 | cost         |      95 |
|    2 | lee  | 2020-03-02 | cash         |     -95 |
|    3 | kent | 2020-03-01 | cost         |     100 |
|    3 | kent | 2020-03-03 | cash         |     -50 |
|    3 | kent | 2020-03-04 | card         |     -50 |
|    4 | iya  | 2020-03-01 | cost         |      80 |
|    4 | iya  | 2020-03-05 | cash         |     -40 |
|    4 | iya  | 2020-03-06 | card         |     -20 |
+------+------+------------+--------------+---------+

then

SELECT uid
     , name
     , date
     , cost
     , cash
     , card
     , total as balance
FROM (
        SELECT 
               name
             , DATE_FORMAT(trans_date, '%b %D') as date  
             , CASE payment_type
                    WHEN 'cash' THEN -payment
                    ELSE '-'
                    END as cash
             , CASE payment_type
                    WHEN 'card' THEN -payment
                    ELSE '-'
                    END as card
             , CASE payment_type
                    WHEN 'cost' THEN payment
                    ELSE '-'
                    END as cost
             , @tot := CASE @previd
                            WHEN uid THEN @tot+payment
                            ELSE payment
                            END as total
             , @previd := uid as uid
        FROM (
                SELECT * 
                FROM payment 
                ORDER BY uid, trans_date
             ) sorted
             JOIN (SELECT @previd:=0, @tot:=0) initialize
) recs;

+------+------+---------+------+------+------+---------+
| uid  | name | date    | cost | cash | card | balance |
+------+------+---------+------+------+------+---------+
|    1 | kim  | Mar 1st | 100  | -    | -    |     100 |
|    1 | kim  | Mar 2nd | -    | -    | 100  |       0 |
|    2 | lee  | Mar 1st | 95   | -    | -    |      95 |
|    2 | lee  | Mar 2nd | -    | 95   | -    |       0 |
|    3 | kent | Mar 1st | 100  | -    | -    |     100 |
|    3 | kent | Mar 3rd | -    | 50   | -    |      50 |
|    3 | kent | Mar 4th | -    | -    | 50   |       0 |
|    4 | iya  | Mar 1st | 80   | -    | -    |      80 |
|    4 | iya  | Mar 5th | -    | 40   | -    |      40 |
|    4 | iya  | Mar 6th | -    | -    | 20   |      20 |
+------+------+---------+------+------+------+---------+

 

[Yohanne]

just wow.. @Barand why you are like that.. so easy to solve.. for me actually i got whole day like 15hrs.. but  actually i follow your 1st statement.. thank you so much..