Android Question Select sum() and total() rounding

[rafaelcamara]

Hello friends
I'm in trouble I'd like someone to help me. I'm running a sql query with select sum () and select total () in the sqlite database and the result is rounding to a smaller one.

code:
conect.db.BeginTransaction
rs = conect.db.ExecQuery("Select total(Valor) as TotalTitulos, " & _
"(Select total(Valor) from Titulos where DataRel < 20181016) as TotalVencidos, " & _
"(Select total(Valor) from Titulos where DataRel >= 20181016) as TotalAVencer " & _
"From Titulos")

result:
TotalVencidos: 133540
TotalAVencer: 4849.67
TotalTitulos : 138390

I ran the same query in the database manager and returned it like this:

TotalVencidos: 133540.35
TotalAVencer: 4849.67
TotalTitulos : 138390.02

Can someone tell me why this difference?

 

[Erel]

Please use [code]code here...[/code] tags when posting code.

Why aren't you using smart string for this?

What is the type of Valor column?

 

[rafaelcamara]

Hi Erel,

The Value column is of type Double.

My intention is to bring the total direct through the sql, but I do not understand why it is returning like this. Does it have to do with the B4A version? I'm using version 8.3

 

[eps]

It's nothing to do with B4A. What variable types are you populating the amounts with in B4A? It looks like a type mismatch or it's converting/CASTing them to an INT/Whole Number.

 

[rafaelcamara]

So this result is from the query itself without loading into a variable the field in the database is double

 

[OliverA]

What code do you use in B4A to display the output? What version of Android? On your PC, what version of SQLite? Are you 100% sure you are using the same datbase on both your PC and on your Android device?

 

[rafaelcamara]

I find it strange why of rounding if the field in the database is DOUBLE, I noticed that it is only rounding when the decimal places is less than or equal (, 50), so much that the TotalAVencer field: 4849.67 returned correct. will be the B4A version?

 

[Mahares]

I find it strange why of rounding if the field in the database is DOUBLE

1. Did you try using REAL for data type instead of double?
2. Did you try to use Round(total(Valor),2) or Total (Round(Valor,2)) to see what happens.
3. Did you try to extract the value as: rs.GetDouble(TotalTitulos)

 

[rafaelcamara]

I extracted the value as: rs.GetDouble (TotalTitles) and it worked thanks for the help, but there was some change in B4A, because I have the application 4 years ago since version 2.7, and only now that it happened or did not realize it before?

 

[OliverA]

only now that it happened or did not realize it before?

Most likely this. Everything relating to SQLite in B4A (the methods that use SQLite) is dependent on the underlying Android implementation. So the only other explanation would be that something changed in Android's implementation of SQLite.

 

[eps]

Could it be something as simple as it only outputs 6 digits?

Can you make the data so that the following is true?

TotalVencidos: 3540.35
TotalAVencer: 4849.67
TotalTitulos : 8390.02

?

Something is substringing it or chopping it or CASTing it or ROUNDing it for some reason.. It might not be obvious what or why but it will be 'obvious' once it's worked out..

Without code of any sort it's almost impossible to help you.

 

[Erel]

You should create a small project that reproduces the issue and upload it. As written above the B4A version is not relevant as B4A just passes the commands to the native SQL engine.