sqlite - sql query not returning the correct MTD cumulative sum

Question

I have the following table "card_txns":

user_sign_month      month      months_since_cust      country      txn_amt
2018-01              2018-01        1                      DE           100
2018-01              2018-02        1                      DE           100
2018-01              2018-03        1                      DE           100
2019-01              2019-01        1                      IN           100
2019-02              2019-02        1                      US           1,000
2019-03              2019-03        1                      DE           1,000
2019-04              2019-04        1                      US           1,000  

I want to see the cumulative sum, total sum by txn_month column for 2019, and the following query is not returning that

SELECT month AS Tx_MONTH,
       SUM(txn_amt) AS Total_transactions_2019,
       (SELECT SUM(txn_amt)
          FROM card_txns AS b
         WHERE a.month >= b.month
           AND a.country = b.country) AS CUM_MTD_Total
  FROM card_txns AS a AND substring(month, 1, 4) = '2019'
 GROUP BY month
 ORDER BY month

The output should look like this:

Tx_MONTH   Total_transactions_2019  CUM_MTD_Total
2019-01    100                      100
2019-02    100                      1100
2019-03    100                      2100
2019-04    100                      3100

I want to have the cumulative sum by month sorted in the above manner, s o 2019-01 should appear first and so on.

question from:https://stackoverflow.com/questions/65924959/sql-query-not-returning-the-correct-mtd-cumulative-sum

Answer 1

You would use window functions:

SELECT month AS Tx_MONTH, SUM(txn_amt) as Total_transactions_2019,
       SUM(txn_amt) OVER (ORDER BY month) as MTD_Total
FROM card_txns ct
WHERE month LIKE '2019-%'
GROUP BY month
ORDER BY month;

Here is a db<>fiddle.