Android Question [SOLVED] SQL average from a selection

Altaar

New Member
Licensed 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
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
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
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
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
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
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
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
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
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