Android Question [SOLVED] SQL average from a selection

[Altaar]

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:

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]

You have to use a GROUP BY statement like

SELECT Avg(nbrgem) AS Avg_nbrgem FROM table1 GROUP BY nbrgem HAVING finditem = ?;

 

[Altaar]

Ok, I tried this, but it does'nt work :

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

 

[eurojam]

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.

"SELECT AVG(nbrgem) AS myaverage FROM table1 GROUP BY nbrgem HAVING finditem = 'apple'"

 

[keirS]

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.

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

 

[DonManfred]

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?

 

[Altaar]

I tried another thing, but it doesn't work :

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

 

[DonManfred]

SPfinditem.Selecteditem

returns an int... For the first it is 0. Will the sql find something with finditem="0"?

EDIT: Sorry, my fault...

 

[sorex]

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

SELECT AVG(nbrgem) AS moygem FROM table1 WHERE finditem = ? GROUP BY nbrgem

 

[klaus]

Try this code:

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.

 

[Altaar]

And the good answer is :

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 :

'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