Getting sum total of column values in SQLite

Question

I have a table in SQLite with this structure:

 String CREATE_TOTAL_PRICE = "CREATE TABLE " + TOTAL_PRICE + "("
                + KEY_TPID + " INTEGER PRIMARY KEY,"
                + KEY_TPRICE + " REAL" + ")";

I am trying to get the sum total of all column values of column KEY_TPRICE. I am using this method which seems to work:

 public int getTotalOfAmount() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery("SELECT SUM(total_price) FROM " + TOTAL_PRICE, null);
        c.moveToFirst();
        int i = c.getInt(0);
        c.close();
        return i;
    }

But the problem comes when the value is being displayed.

You see i have data input as 50.01,5.5 and when getting the sum total i get 55 which is not really the case.

The real total is 55.51 when i run the query externally.

I have tried setting REAL,INTEGER, FLOAT,DOUBLE as my column data type but still

What exactly is rounding up my figures? Please asssist me with suggestions.

have you tried getFloat(0) instead of getInt(0) ?

ρяσѕρєя K
– ρяσѕρєя K

2016-02-17 10:07:03 +00:00
Commented Feb 17, 2016 at 10:07 — ρяσѕρєя K
– ρяσѕρєя K, Commented Feb 17, 2016 at 10:07

Community · Accepted Answer · 2017-05-23 12:32:39Z

1

I think you are receiving 55 because you are reading it as "int"

int i = c.getInt(0);

That's why it is being "converted" to int (55).

According to question https://stackoverflow.com/a/17947203/4860513 , it seems that SUM() will return a number in the same format of the column that you are reading. So, try it to read is a double/float and share the result:

 float i = c.getFloat(0);
 double i = c.getDouble(0);

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Feb 17, 2016 at 10:07

guipivoto

18.7k9 gold badges63 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Farhad · Accepted Answer · 2016-02-17 10:05:46Z

1

You better save the price as TEXT in the database, and when calculating the sum, cast them to double or BigDecimal for accurate result.

answered Feb 17, 2016 at 10:05

Farhad

13.1k6 gold badges36 silver badges60 bronze badges

1 Comment

Steve Kamau Over a year ago

Any consequences of using REAL?

Rakesh · Accepted Answer · 2016-02-17 10:05:51Z

1

Change your return value to double

 public int getTotalOfAmount() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery("SELECT SUM(total_price) FROM " + TOTAL_PRICE, null);
        c.moveToFirst();
        double i = c.getDouble(0);
        c.close();
        return i;
    }

answered Feb 17, 2016 at 10:05

Rakesh

7561 gold badge9 silver badges19 bronze badges

Collectives™ on Stack Overflow

Getting sum total of column values in SQLite

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related