As promised, I’m back.
I have a few rows in my SQLite database, some of the columns have numbers in them which I need to add (column by column).
I have found the method DatabaseRecord.DoubleColumn but I don’t know if it is applicable or, in case, how to use it.
Is there another way to add numbers in columns?
Thank you
SELECT someField, SUM(column1) AS column1, SUM(colum2) as column2 FROM myTABLE GROUP by somefieldThat is one thing SQL is really good at… and if you are going to be doing ANYTHING with a Database, I suggest you learn about it. And if you are not familiar, SQL is NOT unique to Xojo… as a matter of fact if existed long before Xojo was even concieved.
Better use the original documentation (at first, search anywhere else if not enough only):
[quote=434381:@Emile Schwarz]Better use the original documentation (at first, search anywhere else if not enough only):
https://sqlite.org/lang_aggfunc.html#sumunc[/quote]
Naaa… Use https://stackoverflow.com then the Manual and if all fails, rest of the world 
Let me think
there must be a reason this channel is called “Getting started” and not “Hot shot cool dude Xojo expert”.
Right, I am not familiar with Databases and yes, I’m trying to learn using them.
[quote=434392:@Armando SORBI]Let me think
there must be a reason this channel is called “Getting started” and not “Hot shot cool dude Xojo expert”.
Right, I am not familiar with Databases and yes, I’m trying to learn using them.[/quote]
I think you misunderstood us. We are not joking about you or your knowledge, we are just showing sources of knowledge but just posting URLs is boooring. 
And as above, to be fair, SQL is not Xojo
Xojo just gives you a place where you can run SQL
So Xojo tells you how to create a database and how to run SQL in it, but the Xojo documentation is about Xojo, not about SQL
In the same way as AppleScript documentation is from Apple
And Office automation documentation is from Microsoft
And XML documentation (if you use the XMLDocument class) , isnt Xojo documentation either.
Reading the Xojo docs, you might be tempted to create a recordset, then iterate through each row, looking at the value of column1, and adding up as you go.
But SQL is a powerful data manipulation tool which (see Dave’s example) can do the grunt work for you, if you know how to ask.
Sorting, grouping, concatenation, filtering by criteria… all use the SQ Language, not Xojo syntax
[quote=434377:@Armando SORBI]As promised, I’m back.
I have a few rows in my SQLite database, some of the columns have numbers in them which I need to add (column by column).
I have found the method DatabaseRecord.DoubleColumn but I don’t know if it is applicable or, in case, how to use it.
Is there another way to add numbers in columns?
Thank you[/quote]
Armando - How is your database table schema setup (ie. how many columns of what type?
What I normally do in a basic scenario that I use SQL statements to select the data I want out of my SQLite DB table. I then extract this data into a Xojo recordset which gives me my database field data mapped to Xojo properties . At this point your data is now extracted from the SQLite table and you can now use Xojo code to manipulate (add, delete, etc) the data.
If you want to post some code we can guide you through the beginning stages of this to help you make some initial sense of it.
HTH,
Mike
I beg everybody’s pardon if I sounded offensive or rude, it was not my intention. I misunderstood your intentions.
I have a SQL database with 7 columns, one of which is numbers and I need to have a total somewhere in the window.
As I said in a previous post, I’m following the lines of a “Baseball League” webinar example and I managed to have my code working up to the point of setting up the ListBox. I need to retrieve the numbers from it now.
Here’s the link to the code: https://we.tl/t-YzpdRUYaU0
Thank you and, again, no offense intended
Ciao Armando,
ti scrivo in Italiano visto che siamo corregionali (sono di Bologna).
Alcune osservazioni sul tuo progetto e sul tuo database:
la tabella “Ditte” ha tutti i campi di tipo TEXT (con esclusione del campo ID).
Non va bene: il campo “Fattura” presumo sia il numero della fattura, per cui deve essere Integer;
Il campo “Data_Fattura” chiaramente una data, per cui deve essere di tipo Date
Il campo “Importo” chiaramente un campo che conterr dei valori in euro (o altre valute), per cui deve essere Currency
E’ importante assegnare il giusto tipo di dati ad ogni campo, se vuoi utilizzare certe operazioni, e la SUM una di queste: non puoi eseguire la somma di campi di testo.
Progetto. Non capisco cosa volevi fare col pulsante Somma. Quello che posso dirti che per impostare la propriet Text del TextField “TextSomma” con il valore della variabile “dbr” non serve Str(dbr), in quanto dbr gi String: basta fare
TextSomma.Text = dbrInoltre lo statement SQL SELECT * FROM Ditte SUM (Importo) WHERE row= ? non corretto: se vuoi ottenere la somma dei campi “Importo” di tutte le righe devi fare SELECT SUM (Importo) AS Totale FROM Ditte
Ricorda infine che uno statement SQL di tipo SELECT ti restituisce un Recordset che avr un unico campo che si chiama Totale di tipo Currency (lo stesso del campo di cui viene eseguita la somma), per cui dovresti fare
Dim RS As Recordset
RS = DB.SqlSelect(dbr)
Somma = RS.Fields("Totale").CurrencyValueSe mi spieghi bene cosa volevi ottenere vedo di darti le dritte che ti servono.
Buona giornata.
The Test.sqlite file does not have many values to sum from 
The ID (Primary) declaration is bad.
the Test.sqlite seems to not been created correctly. I found Returns in it (not problematic) and \TAB (?) and allnames (TABLE, COLUMNS) between quotes (?).
The Xojo project does not seems to have created the Test.sqlite file: I do not found any code to do that.
I suppose the PushSomma button is meant to make the addition, but the code does not do anything.
Heres a CREATE string that works fine:
CREATE TABLE Ditte(ID INTEGER NOT NULL, Ditta TEXT, Fattura TEXT, Data_Fattura TEXT, Importo TEXT, Data_Pagamento TEXT, Metodo TEXT, Causale TEXT, PRIMARY KEY(ID));Code to create the file beside the application:
[code]Sub Open() Handles Open
// 1. Create the SQLite db file
Dim f As FolderItem
f = New FolderItem(“Test.sqlite”)
Dim db As New SQLiteDatabase
db.DatabaseFile = f
If db.CreateDatabaseFile Then
// proceed With database operations…
Else
MsgBox("Database not created. Error: " + db.ErrorMessage)
Return
End If
// 2. Add a TABLE
Dim sql_cmd As String
sql_cmd = “CREATE TABLE Ditte(ID INTEGER NOT NULL, Ditta TEXT, Fattura TEXT, Data_Fattura TEXT, Importo TEXT, Data_Pagamento TEXT, Metodo TEXT, Causale TEXT, PRIMARY KEY(ID));”
If db.Connect Then
// Set page_size before any other commands are sent
// db.SQLExecute(“PRAGMA page_size = 8192;”)
// Add the TABLE
db.SQLExecute(sql_cmd)
End If
End Sub[/code]
Code above taken from the LR and a bit transformed, then added in the App Event.
Ciao Amando,
hai risolto il tuo problema SQLite?
Per me, il sovraccarico della codifica SQL era troppo difficile e
impossibile capire la sintassi correttamente.
Utilizzo da anni un file BinaryStream per i miei programmi. (un po 'datato ma sempre ok)
Computer per utente singolo - nessun WEB
Dato che so dove sono i campi dati, posso Stream.position
scrivi direttamente e leggi. (Testo / int16 / int32 int64)
Ho gi file con oltre 30.000 record con diversi
i campi di dati di aggiunta interna vengono creati e possono essere suddivisi in frazioni di secondo
accedervi
Ho riscritto il tuo modello di conseguenza
se sei interessato al link
https://www.dropbox.com/s/yzkbfuaeqg63rsm/Test%20fatture-new%20Binary.xojo_binary_project?dl=0
Thanks everybody, I solved most of my doubts and I’m proceeding fairly well.
Grazie Nedi per i suggerimenti.
Rudolf, ho scaricato il link. Provo a studiarlo e vedere come funziona. Grazie.
Emile, I undestand your concern, the sqlite file I uploaded did not have much data in it. I populated it and, with changes in my code, seems to work. Thank you, anyway
@Rudolf: read the direct message I sent you (in this forum).