Search code examples
androiddatabasesqlitecursor

one query for two purposes

I have the following two queries:

A. 

db.query(DBHelper.TABLE, new String[] {
                DBHelper._ID, DBHelper._DATE_AND_TIME,
                DBHelper._SOURCE, DBHelper._MODE,
                "SUM(" + DBHelper._AMOUNT + ")" },
                DBHelper._DATE_AND_TIME + " BETWEEN ? AND ?",
                new String[] { date_min, date_max }, null, null, null, null);

and result of sum goes to textview like this 

 String.valueOf(cursor.getString(4)).

Second query B.

db.query(DBHelper.TABLE, new String[] {
            DBHelper._ID, DBHelper._DATE_AND_TIME,
            DBHelper._SOURCE, DBHelper._MODE,
            DBHelper._AMOUNT }, DBHelper._DATE_AND_TIME
            + " BETWEEN ? AND ?", new String[] { date_min, date_max },
            null, null, null, null);

and result goes to 

 adapter = new SimpleCursorAdapter(this, R.layout.item, cursor, from,
            to, FLAG_AUTO_REQUERY);

What I want is to combine both queries. Close cursor after first query and to use same query (A) for adapter. So far I have added DBHelper._AMOUNT to SELECT of A query but ListView shows only the last entry result (not the whole data). How can I modify query A for showing SUM in TextView and then use same query for adapter.

Solution

  • In a normal query (like B), the database returns a result record for each table record that matches the WHERE filter.

    However, when you are using an aggregate function like SUM(), the database computes a single value from all table records (or from all records in a group if you're using GROUP BY) and returns that as a single result record.

    Therefore, your two queries are fundamentally different and cannot be combined into a single query.

    (Please note that the first four result columns of your query A do not have any meaningful value because the result record is not guaranteed to correspond to any particular record in the original table; you should ask only for the SUM value.)