Sum() function with over not work in Room database android

Question

Can anyone help me the below query is working fine in DB Browser but not work with the Room database. Here are the table and query:

Table Transaction:

id	amount	is_credit

Query:

SELECT * 
FROM 
    (SELECT 
         id, amount, 
         SUM(CASE WHEN is_credit = 1 THEN amount ELSE -amount END) OVER (ORDER BY id) AS balance 
     FROM `Transaction`) 
ORDER BY 
    id DESC;

I have tried this query with SimpleSQLiteQuery but I'm getting error :

E/SQLiteLog: (1) near "(": syntax error

score 0 · Answer 1 · answered May 08 '21 at 07:11

0

You are trying to use SQLite Windows functions i.e OVER the version of SQLite on Android Devices is typically some way behind. You'd need at least API 30 (Android R) for SQLite version 3.28.0 (when Windows functions were introduced). Obviously that would place limitations on the App's install base.

You may wish to see Version of SQLite used in Android?

answered May 08 '21 at 07:11

MikeT

51,415
16
49
68

Understood the problem, Can you help me the alternative solution for the same query how can I archive the same result with another query. – Ajay Valand May 08 '21 at 08:21

score 0 · Accepted Answer · answered May 08 '21 at 09:45

If your version of SQLite is lower than 3.25.0 then you can't use window functions like SUM().

Instead you can do it with a correlated subquery:

SELECT t.id, 
       t.amount,
       (SELECT SUM(CASE WHEN tt.is_credit = 1 THEN 1 ELSE -1 END * tt.amount) FROM `Transaction` tt WHERE tt.id <= t.id) AS balance 
FROM `Transaction` t
ORDER BY t.id DESC;

Or a self join:

SELECT t.id, 
       t.amount,
       SUM(CASE WHEN tt.is_credit = 1 THEN 1 ELSE -1 END * tt.amount) AS balance 
FROM `Transaction` t INNER JOIN `Transaction` tt
ON tt.id <= t.id
GROUP BY t.id
ORDER BY t.id DESC;

See a simplified demo.

Sum() function with over not work in Room database android

2 Answers2