return sum and average using room

Question

My table is structure is as follows:

@Entity(tableName = "userFitnessDailyRecords")

public class UserFitnessDailyRecords {



    @NonNull
    @PrimaryKey
    private Date forDay;
    private int stepCount;
}

The query I am writing for getting sum and average is as follows:

@Dao
public interface UserFitnessDailyRecordsDao {


    @Query("SELECT SUM(stepCount), AVG(stepCount) FROM userFitnessDailyRecords where forDay BETWEEN :startDay AND :endDay ORDER BY forDay ASC")
    UserFitnessDailyRecords getUserFitnessSumAndAverageForLastThirtyDays(Date startDay, Date endDay);


    @Query("DELETE FROM userFitnessDailyRecords")
    void deleteUserFitnessDailyRecord();

}

Upon compiling I get an error unable to return values as columns do not contain sum and average field. How does one return sum and average in this case?

Volant

Answer 1

You are returning a UserFitnessDailyRecords entry from your method getUserFitnessSumAndAverageForLastThirtyDays(). But the columns selected by your query are not returning an object of that kind.

Remember the use of "AS" keyword in your SQL query to generate columns names aliases that matches the names of your POJO variables.

You can return instead a POJO from that method, maybe like this:

The Kotlin way:

data class SumAveragePojo(var total: Float, var average: Float)

The Java way:

class SumAveragePojo
{ 
    public float total;
    public float average;
}

and change the method return type like this:

@Query("SELECT SUM(stepCount) as total, AVG(stepCount) as average FROM userFitnessDailyRecords where forDay BETWEEN :startDay AND :endDay ORDER BY forDay ASC")
SumAveragePojo getUserFitnessSumAndAverageForLastThirtyDays(Date startDay, Date endDay);

Enneahedron

I was already using pojo was not aware of column name alias using "as". This worked :) – Volant