sum() function of Sqlite does not seem to work as expected with room persistence library

Question

I want to calculate sum of weight field of all records in table, I tried:

In Dao class

@Dao
interface FooDAO {
    @Query("SELECT sum(purchaseWeight) FROM myTable")
    suspend fun calculateSumOfAllWeight(): Int    
}

In repository class

class FooRepository(application: Application) {

    private var fooDAO: FooDAO

    private var sumOfAllWeight = MutableLiveData<Int>()

    init {
        // skipping other code
        CoroutineScope(Dispatchers.IO).launch {
             sumOfAllWeight.postValue(fooDAO.calculateSumOfAllWeight())
        }
    }

    fun getSumOfAllWeight(): MutableLiveData<Int> {    
        Log.e("SUM_OF_WEIGHT", " sumOfAllWeight "+ sumOfAllWeight.value)
        return sumOfAllWeight    
    }    
}

but It prints

E/SUM_OF_WEIGHT: sumOfAllWeight null

in the logcat, I have referred This Link for working with sum() in sqlite.

Update

I have searched and found this and thisSO posts and updated

@Query("SELECT sum(purchaseWeight) as value FROM myTable")
suspend fun calculateSumOfAllWeight(): Int

and found This and updated

@Query("SELECT COALESCE(sum(COALESCE(purchaseWeight,0)), 0) From myTable")
suspend fun calculateSumOfAllWeight(): Int

but still the same null is returned

Answer 1

The aggregate functions in sql return null when the table is empty. Only the COUNT function returns 0. You also read the value of a LivaData which is updated asynchronously and you are not sure that the query has already been executed. Try putting a log in the init block after the suspend function to test the returned value.