Android Question [SOLVED] SQL average from a selection

Discussion in 'Android Questions' started by Altaar, Mar 1, 2015.

  1. Altaar

    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:
    Code:
    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 !
     
  2. eurojam

    eurojam Well-Known Member Licensed User

    You have to use a GROUP BY statement like
    Code:
    SELECT Avg(nbrgem) AS Avg_nbrgem FROM table1 GROUP BY nbrgem HAVING finditem = ?;
     
  3. Altaar

    Altaar New Member Licensed User

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

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

    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.
    Code:
    "SELECT AVG(nbrgem) AS myaverage FROM table1 GROUP BY nbrgem HAVING finditem = 'apple'"
     
  5. keirS

    keirS Well-Known Member Licensed User

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

    DonManfred Expert Licensed User

    Aehm... You are giving the object Spinner as value????
    Should it not be the selected item from that spinner as STRING?
     
  7. Altaar

    Altaar New Member Licensed User

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

    Code:
    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
     
  8. DonManfred

    DonManfred Expert Licensed User

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

    EDIT: Sorry, my fault...
     
  9. sorex

    sorex Expert Licensed User

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

    try

    Code:
    SELECT AVG(nbrgem) AS moygem FROM table1 WHERE finditem = ? GROUP BY nbrgem
     
  10. klaus

    klaus Expert Licensed User

    Try this code:
    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 eurojam like this.
  11. Altaar

    Altaar New Member Licensed User

    And the good answer is :
    Code:
    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 :

    Code:
    '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
     
    eurojam and DonManfred like this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice