Question 400211 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – calculating running totals with multiple rows to be consolidated first or the running total would be wrong

SELECT
    pph.accnt_code,
    pph.period, 
    SUM(pph.value_debit) AS debits,
    SUM(pph.value_credit) AS credits,
    SUM(pph.value_debit-pph.value_credit) OVER (ORDER BY pph.accnt_code, pph.period) AS endbal
    FROM trans_tst pph
    GROUP BY pph.accnt_code, pph.period 
    ORDER BY pph.accnt_code, pph.period

result should have beenresulting running totals table
but result obtained was
wrong running total calculation

I created an intermediate table consolidating debits and credits by period and then the running total is correct

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Problem was solved with the following code

    WITH cte1 AS (SELECT accnt_code, period, SUM(value_debit) over(PARTITION BY accnt_code, period) AS debit, SUM(value_credit) over(PARTITION BY accnt_code, period) AS credit,

    SUM(value_debit-value_credit) OVER(ORDER BY accnt_code, period) AS endbal

    FROM trans_tst ) SELECT accnt_code, period, debit, credit, endbal FROM cte1 GROUP BY accnt_code, period ORDER BY accnt_code, period;


  2. 0

    The point to realize here is that the window functions evaluate after GROUP BY. As a result of this, the credit and debit columns are not even available. You should be summing the sums, using SUM() as a window function for the former.

    SELECT
    pph.accnt_code,
    pph.period, 
    SUM(pph.value_debit) AS debits,
    SUM(pph.value_credit) AS credits,
    SUM(SUM(pph.value_debit) - SUM(pph.value_credit))
        OVER (ORDER BY pph.accnt_code, pph.period) AS endbal
FROM trans_tst pph
GROUP BY pph.accnt_code, pph.period 
ORDER BY pph.accnt_code, pph.period;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search