SQLite SUM query in Java on Android

Question

Android and Java beginners. I'm posting a question because there's something I want to implement but can't I received the price value by dividing the order into 1 and 2 and sub into 1 and 2 and 3

I want to find two values using sum function in db.

total1 = (total sum of price values with order 1 and sub = 1) - (total sum of price values with order 1 and sub = 2) - (total sum of price values with order 1 and sub = 3) Total2 = (total sum of price values with order 2 and sub = 1) - (total sum of price values with order 2 and sub = 2) - (total sum of price values with order 2 and sub = 3)

I want to get two values and put total1 and total2 in the textview.

First, I have to write a query in the database, but I don't know what to do. I wonder if it is right to use the cursor like this.

enter image description here

 public int Calsum1() {
        SQLiteDatabase db = this.getWritableDatabase();
        int a=0, b=0, c=0, tot1;

        Cursor cursor1 = db.rawQuery("SELECT SUM(" + (DBHelper.COLUMN_CH_Price) + ") FROM " + DBHelper.TABLE_CHOICE
                                +" WHERE " +DBHelper.COLUMN_CH_SUB = 1 && DBHelper.COLUMN_CH_SUB = 1, null);
        if(cursor1.moveToFirst()) {
           return a =cursor1.getInt(4);
        }

        Cursor cursor2 = db.rawQuery("SELECT SUM(" + (DBHelper.COLUMN_CH_Price) + ") FROM " + DBHelper.TABLE_CHOICE
                +" WHERE " +DBHelper.COLUMN_CH_SUB = 1 && DBHelper.COLUMN_CH_SUB = 2, null);
        if(cursor2.moveToFirst()) {
            return b =cursor2.getInt(4);
        }

        Cursor cursor3 = db.rawQuery("SELECT SUM(" + (DBHelper.COLUMN_CH_Price) + ") FROM " + DBHelper.TABLE_CHOICE
                +" WHERE " +DBHelper.COLUMN_CH_SUB = 1 && DBHelper.COLUMN_CH_SUB = 3, null);
        if(cursor3.moveToFirst()) {
            return c =cursor3.getInt(4);
        }

        tot1= a-(b+c);
        return tot1;
    }

MikeT · Answer

Ignoring the fact that your code will not work hence the red underlined sections in the images.

You could use a query two will retrieve two rows each containing a single value, the first being total1 the second being total2 in a single query.

As an example if the table is called choice and it has 3 columns price, _order and sub and is created using:-

CREATE TABLE IF NOT EXISTS choice (price INTEGER,_order INTEGER, sub INTEGER);

note that order is an SQLite keyword and would be an invalid column name so _order has been used.

Then you could use a query such as:-

SELECT sum(CASE WHEN sub=1 THEN price ELSE -price END) AS total, _order AS indicator FROM choice WHERE _order BETWEEN 1 AND 2 AND sub BETWEEN 1 AND 3 GROUP BY _order ORDER BY _order;

note that in the above each row output (one per order) is the sum of the sub 1 rows - the sum of the sub 2 and the sub 3 rows.

For example using an SQLite tool (Navicat for SQLite in this case) and the following:-

DROP TABLE IF EXISTS choice;
CREATE TABLE IF NOT EXISTS choice (price INTEGER,_order INTEGER, sub INTEGER);
INSERT INTO choice VALUES 
    (10,1,1),(20,1,1),(30,1,1), /* + 60 */
    (11,1,2),(12,1,2),(13,1,2),(14,1,2), /*( - 50) */
    (100,1,3),(101,1,3),(102,1,3), /* - 303 */
    /* total -293 */
    (5,2,1),(10,2,1),(15,2,1),(20,2,1),(25,2,1), /* + 75 */
    (1000,2,2),(1001,2,2), /* - 2001 */
    (10000,2,3),(12000,2,3) /* - 22000 */
;
SELECT 
    sum(
        CASE 
            WHEN sub=1 THEN price 
            ELSE -price 
        END) AS total,
    _order AS indicator 
FROM choice 
WHERE _order BETWEEN 1 AND 2 AND sub BETWEEN 1 AND 3 
GROUP BY _order
ORDER BY _order /* not really required */
;

DROP TABLE IF EXISTS choice; /* cleanup test */

Results in:-

The indicator column indicates which order the value is for

So your method could be along the lines of

@SuppressLint("Range")
public int[] CalsumBoth() {
    int[] rv = new int[2];
    SQLiteDatabase db = this.getWritableDatabase();

    Cursor csr = db.rawQuery(
            "SELECT " +
                    "sum(CASE WHEN sub=1 THEN price ELSE -price END) AS total, " +
                    "_order AS indicator " +
                    "FROM choice " +
                    "WHERE _order BETWEEN 1 AND 2 AND sub BETWEEN 1 AND 3 " +
                    "GROUP BY _order " +
                    "ORDER BY _order;",
            null);
    if (csr.getCount() == 1) {
        csr.moveToFirst();
        rv[0] = csr.getInt(csr.getColumnIndex("_order"));
        csr.moveToNext();
        rv[1] = csr.getInt(csr.getColumnIndex("_odrer"));
    }
    csr.close();
    return rv;
}

note that this will return an int[] with 2 elements, the first (element 0) with total1, the second element (element 1) with total 2.
note you should ALWAYS close Cursors when done with them.
- if you leave too many open then an exception will occur.
obviously you can amend the above to introduce your constants
note should there not be the expected/required 2 rows, then both elements of the returned int[] will be 0 (you may want to use a value other than 0, or perhaps another way (3rd element) to indicate that the data was not extracted).

Şageldi Alyýew · Answer

I think Room database better than Sqlite. Room database also create Sqlite database, but Room db is easy to use. Room is Sqlite ORM like the Node JS Prisama. You don't need Cursor if you use Room db, because Room database return List,Object class or variable when you select something

You can find Room database implementation here

I write one example here, If you need

Caution: Although these APIs are powerful, they are fairly low-level and require a great deal of time and effort to use: There is no compile-time verification of raw SQL queries. As your data graph changes, you need to update the affected SQL queries manually. This process can be time consuming and error prone. You need to use lots of boilerplate code to convert between SQL queries and data objects. For these reasons, we highly recommended using the Room Persistence Library as an abstraction layer for accessing information in your app's SQLite databases.

SQLite SUM query in Java on Android

2 Answers