sum and group by do not work in android roomdatabase Kotlin

Question

I have a roomdatabase with 4 tables, I want to use GROUP BY and also SUM both in ONE query. so what I had done till now:

@Query("SELECT *, SUM(increase) FROM transactions GROUP BY user_id")
fun groupBy(): LiveData<List<Transactions>>?

But SUM doesnt work(It shows the first increase by user_id.
For instanse: I have 2 users named MAX and Amir with user id 1 and 2

Amir(userId 1) submit 100$ increase and again 50$ increase.
Max(userId2) submit 80$ increase and again 10$ increase.

Please look at the result:

D/TAG: groupBy: 1  100
D/TAG: groupBy: 2  80

It supposed to show:

D/TAG: groupBy: 1  150
D/TAG: groupBy: 2  90

one point: I have a field in database that named trans_id which stands for transactionId. Each time I submit increase or decrease or anything else thet related to user and money my app automaticly genereates one id and it is transactionId each time it autmaticly generate transactionId but It is not as same as last one. Where I use It? When I want to get all user's transactions

Function where I read data from(in user list fragment):

 private fun groupBy() {
    mUserListViewModel.groupBy()?.observe(viewLifecycleOwner, {
        mUserListViewModel.group.value = it
        it.forEach {
            Log.d("TAG", "groupBy: ${it.userId} // ${it.increase}")
         }
    })
}

My viewModel:

 val group = MutableLiveData<List<Transactions>>()
fun groupBy(): LiveData<List<Transactions>>? { 
    return mTransactionDAO.groupBy()
}

Data class:

data class Transactions(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "trans_id")
    var transId: Long = 0L,
    @ColumnInfo(name = "user_id", index = true) // <<<<< best to have an index on the column, not required
    var userId: Long?,
    @ColumnInfo(name = "create_date")
    var createDate: String?,
    @ColumnInfo(name = "bank_id")
    var bankId: Long?,
    @ColumnInfo(name = "description")
    var description: String?,
    @ColumnInfo(name = "increase")
    var increase: String?,
    @ColumnInfo(name = "decrease")
    var decrease: String?,
    @ColumnInfo(name = "loan_number")
    var loanNumber: String?,
    @ColumnInfo(name = "total")
    var total: String?,
    @ColumnInfo(name = "type")
    var type: String?,
    @ColumnInfo(name = "loan_id")
    var loanId: Long?
)

My database

If you need more code, let me know in comments section

Answer 1

Your code does not read the summed increase but the column increase of your table because you use * which selects all the columns.

The correct way to write the query is:

@Query("SELECT user_id, SUM(increase) AS increase FROM transactions GROUP BY user_id")
fun groupBy(): LiveData<List<userTotals>>?

so that you get 2 columns: the user_id and the the total of increase aliased as increase.

You also need to create a new class for the results of the query:

data class userTotals(val user_id: Long?, val increase: Long?)