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)
...

 

[Erel]

No such thing as "not supported in B4A". It is the internal native SQLite engine that does all the work.

 

[drgottjr]

maybe this ?

 

[aeric]

maybe this ?

It is much cleaner to use GROUP BY

 

[Mahares]

It is much cleaner to use GROUP BY

Are you sure OVER PARTTION works in SQLite. Have you tried it. I could never get it to work either from curiosity point of view although I never needed it because I use Group By. Do you have a working sample for Over Partition?

 

[aeric]

Are you sure OVER PARTTION works in SQLite. Have you tried it. I could never get it to work either from curiosity point of view although I never needed it because I use Group By. Do you have a working sample for Over Partition?

Hi @Mahares are you quoting @Kanne ?
I mean I prefer to use GROUP BY. In my profession life if I could remember, I only use GROUP BY to get the aggregate results I needed. The OVER PARTITION keywords maybe appeared once during my education time but since that I never use it. Of course I never tried it in SQLite or DBBrowser.

He has posted a screenshot meaning he proved that OVER PARTITION works for SQLite in DB Browser.

 

[Erel]

The answer is here: https://www.sqlite.org/windowfunctions.html#history
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.

 

[Kanne]

"group by" does not help if I need subtotals. working example is in the attached picture: but it is within the "DB Browser" on my PC, not Android.
Found that the so named "windows"-functions in SQLite were added with V 3.25: Introduction to Window Functions
but on my phone runs Android V7 with SQlite 3.9.2 and so seems to not support these functions.
I could get it run by replacing the OVER statements by subqueries
(what is quite tricky, because you have to pay attention to limit the data to the same where clause or you will get strange results !)

                    sQuery.Initialize
                    sQuery.append(" SELECT Spieltag.ID_Spieltag as Spieltag, Spieltag.Datum, Spieler, Punkte, Platz")
'                    sQuery.append(" ,ifnull(count(*)    OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) as Listen")
'                    sQuery.append(" ,ifnull(SUM(Punkte) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) as Punkte_Summe")
'                    sQuery.append(" ,ifnull(SUM(Platz)  OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) as Platz_Summe")
'                    sQuery.append(" ,printf(""%.2f"",ifnull(AVG(Punkte) OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) ) as Punkte_Schnitt")
'                    sQuery.append(" ,printf(""%.2f"",ifnull(AVG(Platz)  OVER (PARTITION BY spieler ORDER BY Spieler_Spieltag.ID_Spieltag),0) ) as Platz_Schnitt")
'                     SQL-Unterabfrage statt OVER (Partition ...):
                    sText = " from Spieler_Spieltag as ZW1 JOIN Spieltag as ZW2 ON ZW1.ID_Spieltag = ZW2.ID_Spieltag WHERE ZW1.Spieler = Spieler_Spieltag.Spieler and ZW1.ID_Spieltag <= Spieltag.ID_Spieltag and ZW2.Gruppentext='" & sGruppe & "'"
                    sQuery.append(" ,printf(""%.0f"",(Select count(*)     " & sText & " )) as ANZ_Listen")
                    sQuery.append(" ,printf(""%.0f"",(Select SUM (Punkte) " & sText & " )) as SUM_Punkte")
                    sQuery.append(" ,printf(""%.2f"",(Select AVG (Punkte) " & sText & " )) as AVG_Punkte")
                    sQuery.append(" ,printf(""%.2f"",(Select SUM (Platz)  " & sText & " )) as SUM_Platz")
                    sQuery.append(" ,printf(""%.2f"",(Select AVG (Platz)  " & sText & " )) as AVG_Platz")
                    sQuery.append(" FROM Spieler_Spieltag ")
                    sQuery.append(" JOIN Spieltag ON Spieler_Spieltag.ID_Spieltag = Spieltag.ID_Spieltag ")
                    sQuery.append(" WHERE Spieltag.Gruppentext='" & sGruppe & "'")
                    sQuery.append(" ORDER BY Spieler_Spieltag.Spieler, Spieltag.ID_Spieltag")
                    Log("SQL: " & sQuery.ToString)

Question:
is the SQlite version fixed to the Android version installed on the phone or can it be updated by hand ?!

 

[Erel]

is the SQlite version fixed to the Android version installed on the phone or can it be updated by hand ?!

Why are you using '?!' after each of your questions?!

You cannot update the SQLite version. You can try to include your own version of SQLite and access it with JdbcSQL. You will encounter other problems and it doesn't worth the efforts.

 

[Kanne]

don't know why using it ... maybe because it seems strange for me spending so much time with runtime errors.
Could get it run with the workaround - thanks for support.

 

[Mahares]

spending so much time with runtime errors

You would have served yourself better if you used String Literals in your SQL statement as opposed to string builder. The syntax would have been easier to understand and debug. Also, using aliases for the tables is helpful. You used aliases in part of it but not all. Notice I have used the ROUND function instead of printf. Here is an excerpt to give you an idea of what it would have looked like, untested of course:

Dim sGruppe As String ="test"
    Dim Gruppe(6) As String
    For i=0 To 5
        Gruppe(i) = sGruppe
    Next
    Dim sText As String = $" from Spieler_Spieltag As ZW1 JOIN Spieltag As ZW2 ON ZW1.ID_Spieltag = ZW2.ID_Spieltag 
    WHERE ZW1.Spieler = Spieler_Spieltag.Spieler And ZW1.ID_Spieltag <= Spieltag.ID_Spieltag 
    And ZW2.Gruppentext= ? "$
    
    Dim MyQuery As String =$" SELECT Spieltag.ID_Spieltag as Spieltag, Spieltag.Datum, Spieler, Punkte, Platz
    ,ROUND(Select count(*)    ${sText},0) as ANZ_Listen
    ,ROUND(Select SUM(Punkte) ${sText},0) as SUM_Punkte
    ,ROUND(Select AVG(Punkte) ${sText},2) as AVG_Punkte
    ,ROUND(Select SUM(Platz)  ${sText},2) as SUM_Platz
    ,ROUND(Select AVG(Platz)  ${sText},2) as AVG_Platz
    FROM Spieler_Spieltag 
    JOIN Spieltag ON Spieler_Spieltag.ID_Spieltag = Spieltag.ID_Spieltag 
    WHERE Spieltag.Gruppentext=?
    ORDER BY Spieler_Spieltag.Spieler, Spieltag.ID_Spieltag"$
    Dim rs As ResultSet
    rs=Starter.SQL1.ExecQuery2(MyQuery, Gruppe)

 

[Kanne]

thanks for advice.
you are right:
the sql statements are to be optimized ... I'm often working in SQL management studio where I have to quick enter SQL requests and don't have time to write stored proc's or use var's for getting results

I used printf for problems with sorting a table ... in first try I wanted to include signing and leading spaces what is not possible by round; meanwhile Klaus has enhanced table.bas for sorting numeric values and now my printf is obsolet and may be exchanged by SQL-round

 

[aeric]

You would have served yourself better if you used String Literals in your SQL statement as opposed to string builder. The syntax would have been easier to understand and debug. Also, using aliases for the tables is helpful. You used aliases in part of it but not all. Notice I have used the ROUND function instead of printf. Here is an excerpt to give you an idea of what it would have looked like, untested of course:

Dim sGruppe As String ="test"
    Dim Gruppe(6) As String
    For i=0 To 5
        Gruppe(i) = sGruppe
    Next
    Dim sText As String = $" from Spieler_Spieltag As ZW1 JOIN Spieltag As ZW2 ON ZW1.ID_Spieltag = ZW2.ID_Spieltag
    WHERE ZW1.Spieler = Spieler_Spieltag.Spieler And ZW1.ID_Spieltag <= Spieltag.ID_Spieltag
    And ZW2.Gruppentext= ? "$
  
    Dim MyQuery As String =$" SELECT Spieltag.ID_Spieltag as Spieltag, Spieltag.Datum, Spieler, Punkte, Platz
    ,ROUND(Select count(*)    ${sText},0) as ANZ_Listen
    ,ROUND(Select SUM(Punkte) ${sText},0) as SUM_Punkte
    ,ROUND(Select AVG(Punkte) ${sText},2) as AVG_Punkte
    ,ROUND(Select SUM(Platz)  ${sText},2) as SUM_Platz
    ,ROUND(Select AVG(Platz)  ${sText},2) as AVG_Platz
    FROM Spieler_Spieltag
    JOIN Spieltag ON Spieler_Spieltag.ID_Spieltag = Spieltag.ID_Spieltag
    WHERE Spieltag.Gruppentext=?
    ORDER BY Spieler_Spieltag.Spieler, Spieltag.ID_Spieltag"$
    Dim rs As ResultSet
    rs=Starter.SQL1.ExecQuery2(MyQuery, Gruppe)

Would the query works without using GROUP BY all the non aggregate columns?

 

[Kanne]

running subtotals are not possible by using "group by" - you have to use window-functions or subqueries.
With "group by" you only define on which level you want your output.
The statement results in an SQL-Error - it seems, that the variables are not replaced in correct manner;
string literals might be useful, but I don't have experince in that - so I'm doing thing where I know what I'm doing and build my SQL by hand.

** Activity (main) Pause, UserClosed = false **
22:01:12.998: Pause Main
** Activity (auswahl) Create, isFirst = true **
** Activity (auswahl) Resume **
Error occurred on line: 113 (Auswahl)
android.database.sqlite.SQLiteException: near "Select": syntax error (code 1): , while compiling: SELECT Spieltag.ID_Spieltag as Spieltag, Spieltag.Datum, Spieler, Punkte, Platz
,ROUND(Select count(*) from Spieler_Spieltag As ZW1 JOIN Spieltag As ZW2 ON ZW1.ID_Spieltag = ZW2.ID_Spieltag
WHERE ZW1.Spieler = Spieler_Spieltag.Spieler And ZW1.ID_Spieltag <= Spieltag.ID_Spieltag
And ZW2.Gruppentext= ? ,0) as ANZ_Listen
,ROUND(Select SUM(Punkte) from Spieler_Spieltag As ZW1 JOIN Spieltag As ZW2 ON ZW1.ID_Spieltag = ZW2.ID_Spieltag
WHERE ZW1.Spieler = Spieler_Spieltag.Spieler And ZW1.ID_Spieltag <= Spieltag.ID_Spieltag
And ZW2.Gruppentext= ? ,0) as SUM_Punkte
,ROUND(Select AVG(Punkte) from Spieler_Spieltag As ZW1 JOIN Spieltag As ZW2 ON ZW1.ID_Spieltag = ZW2.ID_Spieltag
WHERE ZW1.Spieler = Spieler_Spieltag.Spieler And ZW1.ID_Spieltag <= Spieltag.ID_Spieltag
And ZW2.Gruppentext= ? ,2) as AVG_Punkte
,ROUND(Select SUM(Platz) from Spieler_Spieltag As ZW1 JOIN Spieltag As ZW2 ON ZW1.ID_Spieltag = ZW2.ID_Spieltag
WHERE ZW1.Spieler = Spieler_Spieltag.Spieler And ZW1.ID_Spieltag <= Spieltag.ID_Spieltag
And ZW2.Gruppentext= ? ,2) as SUM_Platz
,ROUND(Select AVG(Platz) from Spieler_Spieltag As ZW1 JOIN Spieltag As ZW2 ON ZW1.ID_Spieltag = ZW2.ID_Spieltag
WHERE ZW1.Spieler = Spieler_Spieltag.Spieler And ZW1.ID_Spieltag <= Spieltag.ID_Spieltag
And ZW2.Gruppentext= ? ,2) as AVG_Platz
FROM Spieler_Spieltag
JOIN Spieltag ON Spieler_Spieltag.ID_Spieltag = Spieltag.ID_Spieltag
WHERE Spieltag.Gruppentext=?
ORDER BY Spieler_Spieltag.Spieler, Spieltag.ID_Spieltag
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)

 

[Kanne]

@aeric: I tested the statements again:
the "round" has to be inside of the subquery:

,(Select round(count(*),2) ${sText},0) as ANZ_Listen

Defining a param-array and changing it's size for each additional subquery does not make sense for me.
And what is even more strange for me: strings have to be passed without the

'

which normaly have to be used by SQL.
So I prefer the standard SQL-calls without param-array ...

 

[Kanne]

P.S.: round vs printf
I would also prefer to use "round" because it's native SQL - but the results are NOT the same:
"round" will discard trailing zeroes, so for a unique result in my tableview the "printf" is a better solution

 

[OliverA]

Defining a param-array and changing it's size for each additional subquery does not make sense for me.
And what is even more strange for me: strings have to be passed without the which normaly have to be used by SQL.
So I prefer the standard SQL-calls without param-array

The whole purpose of parameter arrays is to prevent SQL injection attacks. As long as you think you are capable of sanitizing the input you use to create your SQL statements (that go beyond quote marks), feel free to ignore (at your own risk) parameterized queries.
Edit: a little over the top

 

[aeric]

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

 

[aeric]

So I just learned to use B4XTable with this data

Round is not required since we need to reformat the number in B4XTable.

 

[Kanne]

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.