CODEWITHSUNDEEP
androidsqliteandroid-sqlite
seekingStillness
seekingStillness

Reputation: 5093

How to get daily sum in SQLite

I have a simple SQLite database with two columns.

Col 1 SimpleDateFormat("yyyyDDD") For example 2017001 (for jan 1st, 2017)

Col 2 int of hourly occurances

So each day has a unique code and each day has 24 rows of varying occurances. How can I get the sum of occurrences for each day sent to an ArrayList(Float)? Eventually this ArrayList will populate an MPAndroidChart. I've successfully pulled the data (without the sum) with the following code. But getting the daily sum with help of my date code has eluded me for days. I've tried many variations of GROUP BY and SUM only to have each crash.

public ArrayList<Float> getOccChartData(){
    SQLiteDatabase db = this.getWritableDatabase();
    ArrayList<Float> yNewData = new ArrayList<>();
    String query = "SELECT OCC FROM user_table ORDER BY ID DESC LIMIT 96";
    Cursor c = db.rawQuery(query, null);
    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
        yNewData.add(c.getFloat(c.getColumnIndex("OCC")));
    }
    c.close();
    return yNewData;
}

Upvotes: 2

Views: 723

Answers (1)

MikeT
MikeT

Reputation: 56943

It's unclear whether or not you want the sums instead of or with the rows, so assuming that you just want the sums, then perhaps you could do:-

public ArrayList<Float> getOccChartData(){
    SQLiteDatabase db = this.getWritableDatabase();
    ArrayList<Float> yNewData = new ArrayList<>();

    String query = "SELECT SUM(OCC) AS OCC FROM user_table GROUP BY ID ORDER BY ID DESC"

    Cursor c = db.rawQuery(query, null);
    while(c.moveToNext) {
        yNewData.add(c.getFloat(c.getColumnIndex("OCC")));
    }
    c.close();
    return yNewData;
}

Example

For example assuming your table has (not 24 rows per table for brevity) :-

enter image description here

The the above would result in the cursor containing the following :-

enter image description here

Note! reversal due to ORDER BY DESC (so latest data first). i.e. for 2017001, 15 + 20 + 30 + 40 = 105 the last row.

yNewData would have 3 elements accordingly:-

  • yNewData[0] would be 48,
  • yNewData[1] would be 50 and
  • yNewData[2] would be 105.

Alternative considerations

If you wanted a range of days then you change the SELECT to be something like :-

SELECT SUM(OCC) FROM user_table WHERE ID BETWEEN 2017001 AND 2017002 GROUP BY ID ORDER BY ID DESC

Where the values 2017001 and 2017002 would be generated according to the required logic and placed into the query string.

In which case the resultant returned data would be :-

  • yNewData[0] would be 50 and
  • yNewData[1] would be 105.

If you want all intermediate values as well as the sums then matters would be more complex as in effect you are returning two types of data in a single dimension array.

The above as an SQL Fiddle

Here's the above as a SQL Fiddle

Upvotes: 2

Related Questions