Android Question Sum a Numeric Column of a SQLite Table by Grouping every N Number of Records

[Mahares]

I would like to sum a numeric column in a SQLite table every say 10 records. Say as an example, I have 75 records and I want the 1st 10 records summed together, then the next 10 and so on, the last 5 records will be summed together. I tried to look for a way to do it using Group BY or Count or LIMIT OFFSET, but to no avail. The records have no common column to group them by.
I can do it using StringBuilder and CASE WHEN condition2 THEN 1, CASE WHEN condition2 THEN 2, etc. , by creating a rank column to GROUP BY, but I was wondering if there is a direct way in SQLite

 

[edgar_ortiz]

I do NOT think that from SQLite, it can be solved.

I think you should get the dataset and then do a routine in the app to generate the information

 

[Erel]

Don't worry about performance. It will be very fast.

 

[stevel05]

If you had to do it in SQLite, you could probably create a temporary table copy the data over and add column for the index or just use the temporary table as an index, then use that to get the rows you needed. But that would just be overkill as you can do it in B4x, which is probably easier anyway.

 

[rraswisak]

I would like to sum a numeric column in a SQLite table every say 10 records. Say as an example, I have 75 records and I want the 1st 10 records summed together, then the next 10 and so on, the last 5 records will be summed together. I tried to look for a way to do it using Group BY or Count or LIMIT OFFSET, but to no avail. The records have no common column to group them by.
I can do it using StringBuilder and CASE WHEN condition2 THEN 1, CASE WHEN condition2 THEN 2, etc. , by creating a rank column to GROUP BY, but I was wondering if there is a direct way in SQLite

since you not provide table structure and data sample, you can try this within your case;

This sample will sum every two rows (/2)

SQL:

SELECT tableno, description, member, SUM(member) OVER (PARTITION BY (tableno-1)/2) AS summary FROM ttable

Result:

 

[Mahares]

you can try this within your case;

I tried it on my data

Dim s As String="SELECT  rowid, sum(c0) OVER (PARTITION BY (rowid-1)/20) AS rank FROM data"

and got this error:

android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: SELECT rowid, sum(c0) OVER (PARTITION BY (rowid-1)/20) AS rank FROM data

If your think your code works in SQLite, perhaps you can include the full code or project you managed to show in the previous post, in case I am messing up somewhere. I managed to make my project work using the CASE WHEN condition in SQLite, but if your code works, I would rather use it.

 

[Mahares]

since you not provide table structure and data sample, you can try this within your case;

OVER (PARTITION is a windows function that was added in SQLite version 3.25.0. I tested your code on two devices; one with 3.19.4 and one with 3.22.0. Could it be that the reason it worked for you, is that you have a newer version of SQLite in your device that is 3.25.0 or higher.

 

[rraswisak]

windows function that was added in SQLite version 3.25.0

You're right, and had clear explanation here after try to solve the problem without trying first on android device. (sorry for that)

The post #5 above use DB Browser for SQLite which currently use 3.32.2 version of sqlite db. While trying on android device same error occur.

While android native sqlite equip with version below 3.25.x the OVER PARTITION feature can't be used at the moment.

 

[OliverA]

Maybe

GROUP BY CAST(((rowid - 1)/10) AS INT)

 

[mc73]

That's exactly what's needed, nice one Oliver.

Maybe

GROUP BY CAST(((rowid - 1)/10) AS INT)

 

[Mahares]

Maybe

You were on the right track. I tweaked it every so slightly. You and my old time friend mc73 have the right idea. Who needs to wait till SQLite version 3.25 gets ported to devices; no one.

Dim s As String= "SELECT rowid , sum(c0) As c FROM data GROUP BY (rowid - 1)/10 ORDER BY rowid"

Let me add that as I mentioned in my previous posts it works for me using the SQLite SELECT CASE WHEN conditions ELSE THEN without performance problems, but I like this one liner more.