Android Question [SOLVED] SQL average from a selection

Altaar

New Member
Licensed User
Longtime User
Hi !

I tried to calculate an average from a selection. The selection depends on the value of a spinner.
The table1 from mydatebase is like this :
mabase.ExecNonQuery("CREATE TABLE table1 (id INTEGER PRIMARY KEY, date DATE , nbrgem BYTE, finditem BYTE)")

I tried this but it doesn't work:
B4X:
Dim myaverage as int
Dim spnFindItem as spinner
myaverage = mydatabase.Execquery2 ("SELECT AVG(nbrgem) FROM table1 WHERE finditem = ?", Array As Object( spnFindItem ))

Thank you for your help !
 

eurojam

Well-Known Member
Licensed User
Longtime User
You have to use a GROUP BY statement like
B4X:
SELECT Avg(nbrgem) AS Avg_nbrgem FROM table1 GROUP BY nbrgem HAVING finditem = ?;
 
Upvote 0

Altaar

New Member
Licensed User
Longtime User
Ok, I tried this, but it does'nt work :

B4X:
mabase.Execquery2 ("SELECT AVG(nbrgem) AS myaverage FROM table1 GROUP BY nbrgem HAVING finditem = ?", Array As Object( spnFindItem.selecteditem))
 
Upvote 0

eurojam

Well-Known Member
Licensed User
Longtime User
it was an example how to make an SQL querying the average. You should not paste my example as it is, you should view it as a starting point to solve your problem.
"Finditem" has which datatype? if it is a character you should quote parameter in the where statement like "....HAVING finditem = '?';" and the "?" should be in your table.
B4X:
"SELECT AVG(nbrgem) AS myaverage FROM table1 GROUP BY nbrgem HAVING finditem = 'apple'"
 
Upvote 0

keirS

Well-Known Member
Licensed User
Longtime User
it was an example how to make an SQL querying the average. You should not paste my example as it is, you should view it as a starting point to solve your problem.
"Finditem" has which datatype? if it is a character you should quote parameter in the where statement like "....HAVING finditem = '?';" and the "?" should be in your table.
B4X:
"SELECT AVG(nbrgem) AS myaverage FROM table1 GROUP BY nbrgem HAVING finditem = 'apple'"

Should use WHERE instead of HAVING. HAVING tests for conditions after the aggregation takes place when used with GROUP BY.
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Dim spnFindItem as spinner
myaverage = mydatabase.Execquery2 ("SELECT AVG(nbrgem) FROM table1 WHERE finditem = ?", Array As Object( spnFindItem ))
Aehm... You are giving the object Spinner as value????
Should it not be the selected item from that spinner as STRING?
 
Upvote 0

Altaar

New Member
Licensed User
Longtime User
I tried another thing, but it doesn't work :

B4X:
Dim moygem As Int
Dim LAmoygem as Label
mabase.Execquery2("SELECT AVG(nbrgem) AS moygem FROM table1 WHERE finditem = ? GROUP BY finditem ", Array As String( SPfinditem.Selecteditem )))
LAmoygem.Text = moygem
 
Upvote 0

sorex

Expert
Licensed User
Longtime User
SELECT AVG(nbrgem) AS moygem FROM table1 WHERE finditem = ? GROUP BY finditem

if you want to use calculations you need to group that field.

try

B4X:
SELECT AVG(nbrgem) AS moygem FROM table1 WHERE finditem = ? GROUP BY nbrgem
 
Upvote 0

klaus

Expert
Licensed User
Longtime User
Try this code:
B4X:
Dim moygem As Double
moygem  = mabase.ExecQuerySingleResult2("SELECT avg(nbrgem) FROM table1 WHERE finditem = ?", Array As String( SPfinditem.Selecteditem ))
For calculations you could use ExecQuerySingleResult2.
Be aware that Date and Byte are not defined in SQLite.
 
Upvote 0

Altaar

New Member
Licensed User
Longtime User
And the good answer is :
B4X:
moygem  = mabase.ExecQuerySingleResult2("SELECT AVG(nbrgem) FROM table1 WHERE finditem = ? GROUP BY finditem", Array As String( SPfinditem.Selecteditem ))

Thank you klaus and all of you!


The complete problem to understand :

B4X:
'declarations
Dim mabase as SQL
Private LAmoygem As Label
Private SPfinditem As Spinner

'structure of the table
mabase.ExecNonQuery("CREATE TABLE table1 (id INTEGER PRIMARY KEY, date DATE , nbrgem BYTE, finditem STRING)")

'to calculate the average when a "filter" is applied :
Dim moygem As Double
moygem  = mabase.ExecQuerySingleResult2("SELECT AVG(nbrgem) FROM table1 WHERE finditem = ? GROUP BY finditem", Array As String( SPfinditem.Selecteditem ))
LAmoygem.Text = moygem
 
Upvote 0
Top