How to get the customer payment count for each row in MYSQL
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 &lt;&gt; p1.payment_id) THEN
           concat('Old(',
                  (SELECT count(*) + 1
                          FROM tbl_payments p2
                               WHERE p2.customer_id = p1.customer_id
                                     AND (p2.payment_datetime &lt; p1.payment_datetime
                                           OR p2.payment_datetime = p1.payment_datetime
                                              AND p2.payment_id &lt; 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 &gt; '2015'
             AND p.payment_datetime &lt; '2019';