CODEWITHSUNDEEP
sqliteandroid-sqlite
Usman Riaz
Usman Riaz

Reputation: 3020

Group by cumulative frequency in sqlite

I have a table named transactions table has following columns transaction_id transaction_type --income/expense transaction_amount account

Now i want a cumulative frequency column which will give me cumulative sum group by transaction_type

I am expecting results somewhat like below

type   |amount|account |cumulative sum| 
------------------------------------------  
expense|100.0 |Personal|    100.0     |
expense|150.0 |Personal|    250.0     |
income |1000.0|Personal|    1000.0    |        
income |1000.0|Personal|    2000.0    |
expense|100.0 |Personal|    350.0     |

Expense have their own cumulative sum and income have their own.

How can this be done?

Upvotes: 0

Views: 347

Answers (1)

CL.
CL.

Reputation: 180030

The easiest way to get the cumulative sums is to compute them manually in your app while you are reading the data from the cursor.

SQL is a set-oriented language, so doing this computation in SQL would be inefficient. If you really want to do it anyway, you have to sum up, for each entry, the amounts of all previous matching entries. (This requires that there is some ordering; there should be a timestamp, but I'm assuming that the IDs are ordered.)

SELECT type,
       amount,
       account,
       (SELECT SUM(amount)
        FROM transactions AS t2
        WHERE t2.type            = transactions.type
          AND t2.transaction_id <= transactions.transaction_id
       ) AS cumulative_sum
FROM transactions
ORDER BY transaction_id;

Upvotes: 1

Related Questions