0

I'm having a problem with TOTAL() in SQLite (on android). It seems that if the result is greater than 6 characters (including decimal places) it rounds and uses E notation.

I am extracting the (text) value of the result using a cursor wrapper:

String balance = getString(getColumnIndex(DebtTable.Cols.balance))

...but the result it produces is incorrect in terms of both value and format.

I'd appreciate any help with this and btw, my sql knowledge is not particularly great.

Thanks in advance

Sammy
  • 35
  • 3

1 Answers1

0

You should use the appropriate Cursor get method. e.g. instead of 

 String balance = getString(getColumnIndex(DebtTable.Cols.balance));

You could use:-

String balance = Double.toString(csr.getDouble(DebtTable.Cols.balance));

or perhaps :- 

String balance = NumberFormat.getCurrencyInstance().format(csr.getDouble(DebtTable.Cols.balance));

Here's an example from How flexible/restricive are SQLite column types? that shows how the different get methods retrieve data (i.e VALUE AS ???? eqautes to method would be get????):-

For Double myREAL = 213456789.4528791134567890109643534276; :-

08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: Column=REAL_COL<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS INT >>213456789<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS LONG >>213456789<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS STRING >>2.13457e+08<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS DOUBLE >>2.134567894528791E8<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS FLOAT >>2.1345678E8<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:  VALUE AS SHORT >>6037<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes:      Unable to handle with getBlob.
MikeT
  • 51,415
  • 16
  • 49
  • 68
  • Thanks, you are correct, I actually figured that out for myself. I used getDouble and then converted it to a string where necessary.How are column types useful though as I've read they do nothing as SQLite store everything as TEXT anyway and they are merely there to help people looking at DB structure. – Sammy Aug 18 '17 at 03:42
  • @Sammy they can be useful but it's rather complex, reading the following a few times, especially the parts on affinity, may be needed [Datatypes In SQLite Version 3 ](https://sqlite.org/datatype3.html). – MikeT Aug 18 '17 at 03:51