Android Question SQLite "OVER PARTTION" not supported in B4A ?!

[Kanne]

Hi,
I wanted to add some statistics to my App and now have the problem, that it seems that "OVER" is not supported by B4A ?!

SELECT Spieler, count(*) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag) as Part_SUM
from  Spieler_Spieltag

Executing the statement in "DB Browser for SQLite" works fine, but in B4A I get an error:

** Activity (auswahl) Resume **
Error occurred on line: 2470 (table)
android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling:
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
...

or

** Activity (auswahl) Resume **
Error occurred on line: 2470 (table)
android.database.sqlite.SQLiteException: near "OVER": syntax error (code 1): , while compiling: SELECT Spieltag.ID_Spieltag as Spieltag, Spieltag.Datum, Spieler, Punkte, Platz ,ifnull(count(*) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) as Listen ,ifnull(SUM(Punkte) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) as Punkte_Summe ,ifnull(SUM(Platz) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) as Platz_Summe ,printf("%.2f",ifnull(AVG(Punkte) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) ) as Punkte_Schnitt ,printf("%.2f",ifnull(AVG(Platz) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) ) as Platz_Schnitt FROM Spieler_Spieltag JOIN Spieltag ON Spieler_Spieltag.ID_Spieltag = Spieltag.ID_Spieltag WHERE Spieltag.Gruppentext='Malle 20/21' ORDER BY Spieltag.ID_Spieltag, Platz
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
...

 

[OliverA]

This feature was added in SQLite v3.25

Now I remember! If you use the SQLCipher version, it has an updated SQLite base. Note: you don’t have to use the encryption portion of that library.
Links:
1) https://www.b4x.com/android/forum/threads/sqlite3-fts5.110369/post-688824
2) https://www.b4x.com/android/forum/threads/android-database-encryption-with-sqlcipher-library.14965/

 

[aeric]

I will take a look on B4XTable, but in the moment I'm very lucky to have flexible table running with help from Klaus.
The SQL-statements you posted may solve the formating when using B4XTable but not the data:
you will not get managed running subtotals by using group statements. So formating was not the request of this thread.

My query is group by Spieler. So the same Spieler would have same value. Not sure how you want to get the Punkte_Summe. If let say you want the Punkte_Summe = SUM by the Datum, then inside the subquery B you can GROUP by Datum.

GROUP By SS.Datum  -- Line #17

WHERE A.Datum = B.Datum -- Line #20

(SELECT SS.Datum,  -- Line #8

View attachment 92014

SELECT
A.Spieltag, A.Datum, A.Spieler, A.Punkte, A.Platz,
B.Listen, B.Punkte_Summe, B.Platz_Summe, B.Punkte_Schnitt, B.Platz_Schnitt
FROM
(SELECT S.ID_Spieltag as Spieltag, S.Datum, SS.Spieler, SS.Punkte, SS.Platz
FROM Spieler_Spieltag SS
JOIN Spieltag S ON SS.ID_Spieltag = S.ID_Spieltag) A,
(SELECT SS.Spieler,
ifnull(count(*), 0) as Listen,
ifnull(SUM(SS.Punkte), 0) as Punkte_Summe,
ifnull(SUM(SS.Platz), 0) as Platz_Summe,
printf("%.2f", ifnull(AVG(Punkte), 0)) as Punkte_Schnitt,
printf("%.2f", ifnull(AVG(Platz), 0)) as Platz_Schnitt
FROM Spieler_Spieltag SS
JOIN Spieltag S ON SS.ID_Spieltag = S.ID_Spieltag
WHERE S.Gruppentext = 'Malle 20/21'
GROUP By SS.Spieler
--ORDER BY SS.Spieler
) B
WHERE A.Spieler = B.Spieler
ORDER BY A.Spieltag, A.Platz

 

[Kanne]

I know what a "grouped by" does. Even grouping by detailed fields does make a running subtotal ! I will now stop discussing that in this thread.

 

[pcicom]

Hi,
I wanted to add some statistics to my App and now have the problem, that it seems that "OVER" is not supported by B4A ?!

SELECT Spieler, count(*) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag) as Part_SUM
from  Spieler_Spieltag

Executing the statement in "DB Browser for SQLite" works fine, but in B4A I get an error:



or

SELECT Spieler,Spieldtag, count(*)  as Part_SUM
from  Spieler_Spieltag GROUP BY 1,2 

or  OPCIONAL ORDER

SELECT Spieler,Spieldtag, count(*)  as Part_SUM
from  Spieler_Spieltag GROUP BY 1,2 ORDER BY 1,2

1 = Field Spieler :       1 is a position field on query select
2 = Field Speldtag  :  2 is a position field on query select


[/QUOTE]

 

[Kanne]

please read the complete thread:
the sqlite-windows-function are available from version 3.25 - so you need a very actual Android on your device, on older versons you get a sql-error (like me - that was the reasdon for this thread)

 

[OliverA]

on older versons you get a sql-error

did you try sqlcipher?

 

[RB Smissaert]

did you try sqlcipher?

It works with the latest SQLCipher version, 3.25.2.

RBS

 

[RB Smissaert]

It works with the latest SQLCipher version, 3.25.2.

RBS

To prove it works try these 3 statements:

create table t0(x integer primary key, y text)

insert into t0 values(1, 'aaa'), (2, 'ccc'), (3, 'bbb')

select x, y, row_number() over (order by y) as row_number from t0 order by y

RBS

 

[Erel]

The question was:
"SQLite "OVER PARTTION" not supported in B4A ?!"

The answer is:
1. No such thing as "not supported in B4A". It is the internal native SQLite engine that does all the work.
2. This feature was added in SQLite v3.25

Now go here: https://developer.android.com/reference/android/database/sqlite/package-summary
You will see that the latest documented version in Android is 3.19.

There was also a remark about the unneeded usage of ?!.

For any other discussion please start a new thread.