How to get the customer payment count for each row in MYSQL

Question

I have a payment system, and want to display the counter of each customer payment (first, second, third ...) and if it's the fist payment, display (New). How can i do this in one query ?

SELECT c.customer_name,
       p.payment_datetime,
       p.amount
FROM tbl_payments p
         LEFT JOIN tbl_customers c ON p.customer_id = c.customer_id
ORDER BY p.payment_datetime DESC;

+---------------+-------------------+--------+
| customer_name | payment_datetime  | amount |
+---------------+-------------------+--------+
| Cus1          | 2015              | 200$   |
| Cus2          | 2016              | 300$   |
| Cus1          | 2017              | 140$   |
| Cus1          | 2018              | 500$   |
+---------------+-------------------+--------+

The output must be

+---------------+-------------------+--------+--------+--------------------------------+
| customer_name | payment_datetime  | amount | status |                                |
+---------------+-------------------+--------+--------+--------------------------------+
| Cus1          | 2015              | 200$   | Old(1) | 1 -> First payment for "cus1"  |
| Cus2          | 2016              | 300$   | New    | New -> "cus2" have one payment |
| Cus1          | 2017              | 140$   | Old(2) | 2 -> Second payment for "cus1" |
| Cus1          | 2018              | 500$   | Old(3) | 3 -> Third payment for "cus1"  |
+---------------+-------------------+--------+--------+--------------------------------+

And on executing this query

SELECT c.customer_name,
       p.payment_datetime,
       p.amount
FROM tbl_payments p
         LEFT JOIN tbl_customers c ON p.customer_id = c.customer_id
WHERE p.payment_datetime > '2015' AND p.payment_datetime < '2019'
ORDER BY p.payment_datetime DESC;

Get the result :

+---------------+-------------------+--------+--------+--------------------------------+
| customer_name | payment_datetime  | amount | status |                                |
+---------------+-------------------+--------+--------+--------------------------------+
| Cus2          | 2016              | 300$   | New    | New -> "cus2" have one payment |
| Cus1          | 2017              | 140$   | Old(2) | 2 -> Second payment for "cus1" |
| Cus1          | 2018              | 500$   | Old(3) | 3 -> Third payment for "cus1"  |
+---------------+-------------------+--------+--------+--------------------------------+

Answer 1

You can use a CASE expression that checks for the existence of another payment of the same customer for each payment. If such a payment exists get the count of previous payments plus 1 and use that in the string indicating and "old" payment. Otherwise return the string indicating a "new" payment.

SELECT c1.customer_name,
       p1.payment_datetime,
       p1.amount,
       CASE
         WHEN EXISTS (SELECT *
                             FROM tbl_payments p2
                             WHERE p2.customer_id = p1.customer_id
                                   p2.payment_id <> p1.payment_id) THEN
           concat('Old(',
                  (SELECT count(*) + 1
                          FROM tbl_payments p2
                               WHERE p2.customer_id = p1.customer_id
                                     AND (p2.payment_datetime < p1.payment_datetime
                                           OR p2.payment_datetime = p1.payment_datetime
                                              AND p2.payment_id < p1.payment_id)),
                  ')')
         ELSE
           'New'
       END status
       FROM tbl_payments p1
            LEFT JOIN tbl_customers c1
                      ON p1.customer_id = c1.customer_id
       WHERE p.payment_datetime > '2015'
             AND p.payment_datetime < '2019';