sqlite DatabaseQuery....function SUM(

[Deraman]

Dear, I urgently need help in sqlite...I like to use DatabaseQuery for function SUM(column)
I tried some thing like....without success...
Debug DatabaseQuery(0,"SELECT * SUM(column1) FROM Table1;")....gives me 0
Debug DatabaseQuery(0,"SELECT * ,SUM(column1) FROM Table;").....gives me 1

Thanks
deraman

[fabulouspaul]

Hi Deraman,

after you send a query with DatabaseQuery() to the database-engine/-server you have to pick up the result with something like GetDatabaseLong() or GetDatabaseFloat().
Just check out the included example in the help section.

When i use databases in my code, i find it useful to check if any error occured within the database transaction. Try DatabaseError() (see help for more).

Btw.: i presume you set up your database-connection right and also that column1 is an numeric field?!

[Deraman]

Thanks! but did not worked. My full code

Code: Select all

UseSQLiteDatabase()
 Filename$="testing.sqlite"
  If CreateFile(0, Filename$)
    Debug "Database file created"
    CloseFile(0)
  EndIf
  If OpenDatabase(0, Filename$, "", "")
    Debug "Connected to PureBasic.sqlite"
    If DatabaseUpdate(0, "CREATE TABLE MACH1 (fault1 Float,fault2 Float,fault3 Float);")
      Debug "Table created"
    EndIf
    CloseDatabase(0)
  EndIf
    If OpenDatabase(0,Filename$,"","")
      re = DatabaseUpdate(0,"INSERT INTO MACH1 (fault1,fault2,fault3) VALUES (1,2,3)")
      re = DatabaseUpdate(0,"INSERT INTO MACH1 (fault1,fault2,fault3) VALUES (10,20,30)")
         re = DatabaseUpdate(0,"INSERT INTO MACH1 (fault1,fault2,fault3) VALUES (15,25,35)")
    CloseDatabase(0)
  EndIf
   
  If OpenDatabase(0,Filename$,"","")
    re=DatabaseQuery(0,"SELECT * FROM MACH1")
    Debug "READING"
    While NextDatabaseRow(0) ; Loop for each records
       Debug GetDatabaseString(0, 1) ; Display the content of the first field      
    Wend
    FinishDatabaseQuery(0)
    CloseDatabase(0)
  EndIf
   If OpenDatabase(0,Filename$,"","")
     Debug DatabaseQuery(0,"SELECT * , SUM(fault2) FROM MACH1") 
     Debug GetDatabaseFloat(0,1)
     FinishDatabaseQuery(0)
    CloseDatabase(0)
  EndIf    

[skywalk]

Try terminating your SQL queries with a semicolon, ';'.

[fabulouspaul]

I slightly modified your code:

Code: Select all

UseSQLiteDatabase()

Debug "Creating database file..."
Filename$="testing.sqlite"
If CreateFile(0, Filename$)
  Debug "  Database file created"
  CloseFile(0)
EndIf

Debug "Creating database table..."
If OpenDatabase(0, Filename$, "", "")
  Debug "  Connected to PureBasic.sqlite"
  If DatabaseUpdate(0, "CREATE TABLE MACH1 (fault1 Float,fault2 Float,fault3 Float);")
    Debug "  Table created"
  Else
    Debug "Error: " + DatabaseError()
  EndIf
  CloseDatabase(0)
EndIf

Debug #CRLF$ + "Inserting some values..."
If OpenDatabase(0,Filename$,"","")
  If DatabaseUpdate(0,"INSERT INTO MACH1 (fault1,fault2,fault3) VALUES (1,2,3)")
    Debug "  Insert 1..."
  Else
    Debug "Error: " + DatabaseError()
  EndIf
    
  If DatabaseUpdate(0,"INSERT INTO MACH1 (fault1,fault2,fault3) VALUES (10,20,30)")
    Debug "  Insert 2..."
  Else
    Debug "Error: " + DatabaseError()
  EndIf
  
  If DatabaseUpdate(0,"INSERT INTO MACH1 (fault1,fault2,fault3) VALUES (15,25,35)")
    Debug "  Insert 3..."
  Else
    Debug "Error: " + DatabaseError()
  EndIf
  CloseDatabase(0)
EndIf

Debug #CRLF$ + "Reading all values from database..."
If OpenDatabase(0,Filename$,"","")
  If DatabaseQuery(0, "SELECT * FROM MACH1")
    While NextDatabaseRow(0)        ; Loop for each records
      Debug GetDatabaseString(0, 0) + #TAB$ + GetDatabaseString(0, 1) + #TAB$ + GetDatabaseString(0, 2) 
      ;                          ^ index of returned values starts with 0!  
    Wend
  Else
    Debug "Error: " + DatabaseError()
  EndIf    
  FinishDatabaseQuery(0)
  CloseDatabase(0)
EndIf

Debug #CRLF$ + "Reading SUM() of 2nd column..."
If OpenDatabase(0,Filename$,"","")
  If DatabaseQuery(0, "SELECT SUM(fault2) FROM MACH1") 
    NextDatabaseRow(0)
    Debug GetDatabaseFloat(0,0)  ; just 1 value is returned: SUM(fault2)
  Else
    Debug "Error: " + DatabaseError()
  EndIf       
  FinishDatabaseQuery(0)
  CloseDatabase(0)
EndIf  

@skywalk: SQLite needs no terminating ";"

[skywalk]

@skywalk: SQLite needs no terminating ";"

That is bad practice and only works in the case of a single query. If you send multiple queries, they must have a terminator. Not good to rely on the db engine to guess when to add the termination.
Using ":memory:" for example code...

Code: Select all

UseSQLiteDatabase()
Debug "Creating database table..."
If OpenDatabase(0, ":memory:", "", "")
  Debug "  Connected to PureBasic.sqlite"
  If DatabaseUpdate(0, "CREATE TABLE MACH1 (fault1 Float,fault2 Float,fault3 Float);")
    Debug "  Table created"
  Else
    Debug "Error: " + DatabaseError()
  EndIf
  Debug #CRLF$ + "Inserting some values..."
  If DatabaseUpdate(0,"INSERT INTO MACH1 (fault1,fault2,fault3) VALUES (1,2,3)")
    Debug "  Insert 1..."
  Else
    Debug "Error: " + DatabaseError()
  EndIf
  If DatabaseUpdate(0,"INSERT INTO MACH1 (fault1,fault2,fault3) VALUES (10,20,30)")
    Debug "  Insert 2..."
  Else
    Debug "Error: " + DatabaseError()
  EndIf
  If DatabaseUpdate(0,"INSERT INTO MACH1 (fault1,fault2,fault3) VALUES (15,25,35)")
    Debug "  Insert 3..."
  Else
    Debug "Error: " + DatabaseError()
  EndIf
  Debug #CRLF$ + "Reading all values from database..."
  If DatabaseQuery(0, "SELECT * FROM MACH1;")
    While NextDatabaseRow(0)        ; Loop for each records
      Debug GetDatabaseString(0, 0) + #TAB$ + GetDatabaseString(0, 1) + #TAB$ + GetDatabaseString(0, 2) 
      ;                          ^ index of returned values starts with 0!  
    Wend
  Else
    Debug "Error: " + DatabaseError()
  EndIf    
  FinishDatabaseQuery(0)
  Debug #CRLF$ + "Reading SUM() of 2nd column..."
  If DatabaseQuery(0, "SELECT SUM(fault2) FROM MACH1;") 
    NextDatabaseRow(0)
    Debug GetDatabaseFloat(0,0)  ; just 1 value is returned: SUM(fault2)
  Else
    Debug "Error: " + DatabaseError()
  EndIf       
  FinishDatabaseQuery(0)
EndIf
CloseDatabase(0)

[Deraman]

Dear, Thanks!!!

Code: Select all

  If DatabaseQuery(0, "SELECT SUM(fault2) FROM MACH1")
    NextDatabaseRow(0)

actually solved my problem....thanks you all again...

Now one more issue...
I like to add Date column and filter data with date range...
....WHERE DateStart >= dd/mm/yy AND DateEND <= dd/mm/yy
Really appreciate your help
Thanks
deraman

[fabulouspaul]

Now one more issue...
I like to add Date column and filter data with date range...
....WHERE DateStart >= dd/mm/yy AND DateEND <= dd/mm/yy
Really appreciate your help
Thanks
deraman

Although you can create fields with date-format ("create table test (mydate date);"), SQLite does not support the date-format as one can suppose - any date is stored as a string and it is not checked to be a valid date.

The date-information you put into those fields should follow the format YYYY-MM-DD. To put the date into your desired format use strftime(). Check out https://sqlite.org/lang_datefunc.html for more information.

Try:

Code: Select all

select * from test where mydate between '2017-12-01' and '2017-12-31';

@skywalk: trailing ; included!

[skywalk]

Good answer fabulouspaul
For dates, I prefer to create a quad variable = Val("YYYYMMDD"+"HHMMSS").
Simpler sorting and comparisons with integer math.

[Deraman]

Thanks to all!

I saved date in column1/fault1 as quad .... converting date to quad value as...

Code: Select all

Date$ = FormatDate("%yy%mm%dd", Date())
x.q=Val(Date$)

make queries like

Code: Select all

DatabaseQuery(0, "SELECT SUM(fault2) FROM MACH1 where fault1 between 10 and 15;")

worked!

thanks
deraman

[Deraman]

Dear, really appreciate your help!
I stuck again, so need help!!
Trying to column in existing table...

Code: Select all

    For x=1 To 8
      If DatabaseUpdate(0, "CREATE TABLE "+"MACH"+Str(x)+" (Date INT);"):EndIf
          DatabaseUpdate(0, "ALTER TABLE "+"MACH"+Str(x)+" ADD COLUMN (Fault INT);")
    Next x

Thanks
deraman

[Deraman]

Thanks
done

Code: Select all

    For x=1 To 8
      If DatabaseUpdate(0, "CREATE TABLE "+"MACH"+Str(x)+" (Date INT);"):EndIf
          DatabaseUpdate(0, "ALTER TABLE "+"MACH"+Str(x)+" ADD COLUMN Fault INT;")
    Next x

why it worked without brackets?

[skywalk]

Create complete example code using ':memory:' db like my example above.