How to format Query using ROOM-CodePudding

So I have a table with 4 columns and would like to sum the values of the amount column where isExpense is true and where isExpense is false. I would like to subtract those 2 values and return that sum.

I don't have much experience with SQL other than single line queries so I'm struggling to format this.

         @Query("""
                SELECT SUM (amount) AS INCOME FROM `transaction` WHERE isExpense = 0,
                SELECT SUM (amount) AS EXPENSE FROM `transaction` WHERE isExpense = 1,
                SUM (INCOME - EXPENSE) AS BALANCE
                """)
    fun getTotalBalance(): Flow<Double>?

I could get around this by creating more columns in my table if all else fails.

CodePudding user response：

Use case expressions to do conditional aggregation:

SELECT SUM(case when isExpense = 0 then amount else 0 end) AS INCOME,
       SUM(case when isExpense = 1 then amount else 0 end) AS EXPENSE,
       SUM(case when isExpense = 0 then amount
                when isExpense = 1 then -amount
           end) as BALANCE
FROM `transaction`

WHERE isExpense IN (0, 1) -- Not needed, but might speed things up if there
                          -- are other values than 0 and 1