Average returning wrong value from SQLite database

Question

I am trying to get an average of last 5 (non null) entries from my SQLite database. But somehow, Iam not getting the answer expected.

Heres the query

    db.execSQL( "CREATE TABLE "+FUEL_DATABASE_TABLE +" (" +
                            KEY_ROW_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                            KEY_KM + " LONG NOT NULL, "+
                            KEY_FUEL_QTY + " INTEGER NOT NULL, "+
                            KEY_FUEL_PRICE + " DOUBLE NOT NULL, "+
                            KEY_TOTAL_COST + " DOUBLE NOT NULL, "+
                            KEY_MILEAGE + " DOUBLE NOT NULL, "+
                            KEY_DATE + " DATE NOT NULL, "+
                            KEY_TANK_FULL + " TEXT);"
                            );

    public long createEntry(FuelExpense fe) 
        {
            // TODO Auto-generated method stub


            ContentValues cv = new ContentValues();
            cv.put(KEY_KM, fe.getKm());
            cv.put(KEY_FUEL_QTY, fe.getFuelQty());
            cv.put(KEY_FUEL_PRICE,fe.getFuelPrice());
            cv.put(KEY_TOTAL_COST, fe.getTotalCost());
            cv.put(KEY_MILEAGE, fe.getMileage());
            cv.put(KEY_DATE, fe.getFuelFilledDate());
            cv.put(KEY_TANK_FULL, fe.getTankFullstatus());
            return ourDatabase.insert(FUEL_DATABASE_TABLE, null, cv);
        }





        public String getData()
        {
            // TODO Auto-generated method stub
            String [] columns = new String[]{KEY_ROW_ID, KEY_KM, KEY_FUEL_QTY, KEY_FUEL_PRICE, KEY_TOTAL_COST, KEY_MILEAGE, KEY_DATE,KEY_TANK_FULL};
            fuelCursor =  ourDatabase.query(FUEL_DATABASE_TABLE, columns, null, null, null, null, null);
            String resultSQL= "";

            while(fuelCursor.moveToNext())
            {
                resultSQL = resultSQL + fuelCursor.getString(0) + "         " + fuelCursor.getString(1) + "         " + fuelCursor.getString(2) +"           "+ fuelCursor.getString(3) + "          " + fuelCursor.getString(4) + "          " + fuelCursor.getString(5) + "         " + fuelCursor.getString(6) + "        " + fuelCursor.getString(7) + "\n ";  
            }
            return resultSQL;
        }
 public void getAvgMileage(Prediction pd) 
    {
            predictionCursor = ourDatabase.rawQuery("SELECT ROUND(AVG(_mileage)) FROM fuel_table WHERE _mileage IS NOT NULL ORDER BY _id DESC LIMIT 5", null);
                        if(predictionCursor!=null && predictionCursor.getCount()>0)
                        {
                                predictionCursor.moveToLast();
                                findAvgMileage = predictionCursor.getDouble(0);
                                pd.setpredictionMileage(findAvgMileage);

                        }

    The value obtained from predictionCursor needs to be used for further calculations like so,

            if(ETPredictFuelQty.hasFocus())
                        {
                            if(predictKm!=0)
                            {
                                predictFuetlQty =predictKm/predictionMileage;
                                ETPredictFuelQty.setText(new DecimalFormat("##.##").format(predictFuetlQty));
                            }

                        }

The _mileage has values 10,10,5,3.33,7.5 whose avg should be predictAvgMileage=7.166

I input values for predictKm =1000

SO predictFuelQty = predictKm/predictAvgMileage = 139.54 (Expected answer)

But Iam Getting predictFuelQty = 166.67

Can anybody help me find out whats wrong. Also heres the SQL log

SELECT name, sql FROM sqlite_master WHERE type='table' ;
PRAGMA TABLE_INFO(android_metadata);
PRAGMA TABLE_INFO(sqlite_sequence);
PRAGMA TABLE_INFO(fuel_table);
SELECT name, sql FROM sqlite_master WHERE type='index' 
SELECT rowid, *  FROM android_metadata ORDER BY rowid; 
SELECT name, sql FROM sqlite_master WHERE type='table' ;
PRAGMA TABLE_INFO(android_metadata);
PRAGMA TABLE_INFO(sqlite_sequence);
PRAGMA TABLE_INFO(fuel_table);
SELECT name, sql FROM sqlite_master WHERE type='index' 
SELECT rowid, *  FROM android_metadata ORDER BY rowid; 
SELECT rowid, *  FROM fuel_table ORDER BY rowid;

How can you say it is not correct? What value it does return? what is the value you are expecting? What are the inputs? — AAnkit
– AAnkit, Commented Jul 21, 2013 at 5:29

CL. · Accepted Answer · 2013-07-21 10:19:37Z

1

Your problem is that the LIMIT clause applies to the result of the query, which is already a single record. In other words, the query computes the average of all non-NULL records first, and then ensures that the AVG function does not return more than five values.

Try using a subquery:

SELECT AVG(_mileage)
FROM (SELECT _mileage
      FROM fuel_table
      WHERE _mileage IS NOT NULL
      ORDER BY _id DESC
      LIMIT 5)

answered Jul 21, 2013 at 10:19

CL.

182k18 gold badges241 silver badges282 bronze badges

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

1 Comment

Pooja Gaonkar Over a year ago

That was the exact problem. Than you so much for the explanation as well. It was a great help. Thanks a ton. :)

mu is too short · Accepted Answer · 2013-07-21 06:07:38Z

1

I don't think you're getting 166.67, I think you're actually getting 142.85 (142.86, I'm not sure how Java's DecimalFormat handles rounding and truncation).

Why would I think that? Well, your SQL says this:

SELECT ROUND(AVG(_mileage)) ...

So the average is 7.166 and that becomes 7.0 when you round it. Then 1000/7.0 gives you 142.8571....

In any case, I think you just want to remove that ROUND() call from your SQL. And if you really are getting 166.67 then you'll want to check that your data really is what you think it is after the ORDER and LIMIT or look for a bug elsewhere in your computations.

answered Jul 21, 2013 at 6:07

mu is too short

436k71 gold badges863 silver badges822 bronze badges

6 Comments

AAnkit Over a year ago

Exactly, I tried and was getting 142.85.., And dint get how OP getting 162.67, There might be issue in her Table Schema or somewhere else in the computation, else it could have returned 142.***.

mu is too short Over a year ago

@AAnkit: I'm curious where they're getting the 6.0 from (1000/6.0 == 166.6...). I suspect the SQL isn't getting the data that they think it is getting and then the rounding just further confuses things.

AAnkit Over a year ago

Yes that is why I asked her to check all values in the database. I think this is the case, db is some how returning avg value as 6.0 :). And she has not added even single log to confirm values.

Pooja Gaonkar Over a year ago

@AAnkit yes! I checked the value in predictAvgMileage =6.0

AAnkit Over a year ago

Then go ahead and check why this value is not as expected. Print all values from database, see which value of mileage is wrong? See my answer and follow as suggested.

|

AAnkit · Accepted Answer · 2013-07-21 06:08:48Z

1

I would like more inputs from you.It will in terms tell you what is wrong here, if not I can be able to solve your issue.

1) Add your Table Schema in the question.( Most probably here is your issue)

2) just before you are trying to get avg from Database, try to get and print all the values available in the database. Print them, Add logs in the question.

3) print AVG value you are getting from DB.

findAvgMileage = predictionCursor.getDouble(0); // print findAvgMileage in the logs

add comment when done,so i can be notified.

P.S:- This is not the complete answer, due to length i could not able to add it as a comment. Would update answer as soon as OP update Question with more inputs

answered Jul 21, 2013 at 6:08

AAnkit

27.6k12 gold badges63 silver badges72 bronze badges

4 Comments

Pooja Gaonkar Over a year ago

this is what i get from the predictCursor "findAvgMileage"=5.569285714285714

AAnkit Over a year ago

Now follow step 1 and 2 and your problem would vanish.:)

Pooja Gaonkar Over a year ago

here I have added the code you asked for.. Pl see if its of any relevance.

Pooja Gaonkar Over a year ago

thank you so much for your time. Realized with the help of the solution by CL there was a problem in my query. Thanks again for helping.

Collectives™ on Stack Overflow

Average returning wrong value from SQLite database

3 Answers 3

1 Comment

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related