mORMot Open Source

Mo0211

Member

Registered: 2018-12-30

Posts: 15

Aggregate SUM() Function in mORMot

Dear all,

what is the best way to call

"SELECT SUM(Col1) AS 'sum of col1' FROM table1 WHERE col2 = 'Value' " in mORMot?

i wasn't successfull in finding an example whether in docs nor in examples.

Thank you for your help and a working example is much appreciated!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Aggregate SUM() Function in mORMot

You can use 'sum(col1)' in Field name of your TSQLRest.RetrieveOne  method call for instance.

Mo0211

Member

Registered: 2018-12-30

Posts: 15

Re: Aggregate SUM() Function in mORMot

thanks @ab! i will give it a try

Mo0211

Member

Registered: 2018-12-30

Posts: 15

Re: Aggregate SUM() Function in mORMot

Hey ab,

I tried now what you explained, but it not clear to me, because i can't find a retrieveOne function.
I tried this example:

Form1.Database.Retrieve('src=? AND section=?', [], [source,section], classObj, 'SUM(size)')

As i Understand correct, the classObj will be filled with the related lines from sql.
But how can i access the SUM Column?

Also another question related to this: is it possible to have foreignkeys from referenced sqlrecordclasses in the where clause?
So, the src is a foreignkey which is referenced in another class.
Do you maybe have an example for me for those two cases?

Thanks a lot!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Aggregate SUM() Function in mORMot

You can use OneFieldValue() method, or try to write 'sum(size) as size' if you want to fill the TSQLRecord Size property.

For any complex SQL query, possibly with link between tables, you can use TSQLRest.ExecuteList().