How to calculate running balance for many accounts?
There is something niot right, but you can take it from here.

I replaced your FROM clause, because you need a sorted list to do that coalculation and mariadb  takes the definition, that tables are unsorted literary and eliminates all sorting in subqueries. so you have to circumvent it

Check the numbers, i think you must work on that.



SELECT  id,account_no,account_type,transaction_id,
    DATE_FORMAT(transaction_date,
            '%d/%m/%Y') transaction_date,
    debit_balance,
    credit_balance,
    IFNULL(afp_id, - 1),
    (@current_period_id:=IF(IFNULL(afp_id, - 1) &lt;&gt; @current_period_id,
        IFNULL(afp_id, - 1),
        @current_period_id)) current_period_id,        
    (@running_balance:=IF(@curr_account_no &lt; account_no
            OR (@current_period_id &lt;&gt; IFNULL(afp_id, - 1)
            AND account_type &gt; 5),
        0,
        @running_balance)) prev_runnng_bal
        ,
    (@curr_account_no:=IF(@curr_account_no &lt; account_no,
        account_no,
        @curr_account_no)) curr_account_no
        ,

    (@running_balance:=@running_balance 
    + (debit_balance - credit_balance)) AS running_balance
FROM

    (SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
    ,afp.id as afp_id FROM 
    acc_account_transactions aat
        INNER JOIN
      acc_transactions at ON at.id = aat.transaction_id
        INNER JOIN
    acc_accounts aac ON aac.id = aat.account_no
        LEFT JOIN
    acc_finacial_periods afp ON DATE_FORMAT(transaction_date, '%Y/%m/%d') &gt;= DATE_FORMAT(startdate, '%Y/%m/%d')
        AND DATE_FORMAT(transaction_date, '%Y/%m/%d') &lt;= DATE_FORMAT(enddate, '%Y/%m/%d')
        ORDER BY account_no,aat.id LIMIT 18446744073709551615 ) aat1
    ,(SELECT 
        @running_balance:=0,
            @curr_account_no:=0,
            @current_period_id:=- 1
    ) r



db&lt;>fiddle here

To yxpand the idea

SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
    ,afp.id as afp_id FROM 
    acc_account_transactions aat
        INNER JOIN
      acc_transactions at ON at.id = aat.transaction_id
        INNER JOIN
    acc_accounts aac ON aac.id = aat.account_no
        LEFT JOIN
    acc_finacial_periods afp ON DATE_FORMAT(transaction_date, '%Y/%m/%d') &gt;= DATE_FORMAT(startdate, '%Y/%m/%d')
        AND DATE_FORMAT(transaction_date, '%Y/%m/%d') &lt;= DATE_FORMAT(enddate, '%Y/%m/%d')
        ORDER BY account_no,aat.id LIMIT 18446744073709551615


Builds the basis for the calculation. 

It is a new sub query that sorts the data given so that you can get the wanted result. 

the LIMIT 18446744073709551615  is there,because when mariadb processes the query above, it would remove the order by that is needed.

SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
    ,afp.id as afp_id   


are the columns that are needed for the calculation, if you need more column you first have to include them here. be aware mysql/mariadb doesn't like the same column name, so i have to afp.id as afp_id give the secnd id fiekld an alias.