SQLite SUM query in Java on Android-CodePudding

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.

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

CodePudding user response：

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.