How to get the sum of a column grouped by dates in a Room database?

Question

This is what my table looks like

listOf(
    MyClass(id = 1, cost = 10, qty = 2, date = "10-10-2024"),
    MyClass(id = 2, cost = 11, qty = 1, date = "10-10-2024"),
    MyClass(id = 3, cost = 15, qty = 2, date = "11-10-2024"),
    MyClass(id = 4, cost = 20, qty = 3, date = "12-10-2024")
)

getTotalCostOfDates(["10-10-2024", "11-10-2024"]) should yield ->

 listOf(
    CostClass(cost = 31, date = "10-10-2024"),
    CostClass(cost = 30, date = "11-10-2024")
 )

Answer 1

You need to use the GROUP BY clause of SQL:

@Query("SELECT SUM(cost*qty) AS cost, date FROM myclass WHERE date IN (:dates) GROUP BY date")
suspend fun getTotalCostOfDates(dates: List<String>): List<CostClass>

For each field that you access that is not part of the GROUP BY clause you need to specify how it should be aggregated. Since you want the values to be summed up you need to use SUM().

As return value you can simply use any class that has matching parameters, like this:

data class CostClass(
    val cost: Int,
    val date: String,
)

Since it is not a Room entity it doesn't need to be added to the @Database annotation (unlike MyClass).

Note that Kotlin doesn't support Array literals like ["10-10-2024", "11-10-2024"] outside of annotations, so you need to use something like this instead to call getTotalCostOfDates:

listOf("10-10-2024", "11-10-2024")