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; 

Answer 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)

Answer 2

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

Answer 3

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.