calculate total sum from first month to the last month

Question

How to calculate total sum value from first month to the last?

Example:

i want it to be like this

This is what I've tried, but it doesn't showing the output like the example above.

select 
    left(convert(varchar,txd.OPERATIONALDATE,112),6) MONTH,
    sum(AMOUNTVALUE) TOTAL_A,
    sum(10*AMOUNTVALUE) TOTAL_B,
    sum(10*AMOUNTVALUE) + sum(10*AMOUNTVALUE) TOTAL_C
from tx_details txd
    inner join TX_SELISIH txs 
        on txd.TXID=txs.TXID 
            and txd.OFFICEID=txs.OFFICEID
where txd.operationaldate BETWEEN '20200101' AND '20201231'
    and TXDATE>'20190831' 
    and SELISIHTYPE=1
GROUP BY left(convert(varchar,txd.OPERATIONALDATE,112),6)

Is it even possible to get the result like that? Thanks in advance.

`SUM ( Sum(10*AMOUNTVALUE) + sum(10*AMOUNTVALUE) ) OVER ( ORDER BY left(convert(varchar,txd.OPERATIONALDATE,112),6) )` — Squirrel, Apr 26 '21 at 06:38
@Squirrel thank you for your response sir, i use your code and modify it to SUM ( Sum(10*AMOUNTVALUE) ) OVER ( ORDER BY left(convert(varchar,txd.OPERATIONALDATE,112),6) ) and it works like a charm — Didit Praditya, Apr 26 '21 at 07:46

sa-es-ir · Accepted Answer · 2021-04-26T08:22:27.560

Just use this:

SELECT 
    left(convert(varchar,txd.OPERATIONALDATE,112),6) MONTH,
    sum(AMOUNTVALUE) TOTAL_A,
    sum(10*AMOUNTVALUE) TOTAL_B,
    SUM ( Sum(10*AMOUNTVALUE) + sum(10*AMOUNTVALUE) ) OVER ( ORDER BY left(convert(varchar,txd.OPERATIONALDATE,112),6) ) TOTAL_C
from tx_details txd
    inner join TX_SELISIH txs 
        on txd.TXID=txs.TXID 
            and txd.OFFICEID=txs.OFFICEID
where txd.operationaldate BETWEEN '20200101' AND '20201231'
    and TXDATE>'20190831' 
    and SELISIHTYPE=1
GROUP BY left(convert(varchar,txd.OPERATIONALDATE,112),6)

calculate total sum from first month to the last month

1 Answers1