Trying to get sum from Room database column

Question

This is my first question here and I'm beginner in coding so forgive me if my question is not very clear.

I have stored wages in Room database column called "wage" and the datatype is double. There is wages from different days and I would like to show total from those double numbers in my Activity. If tried many different ways to do it, but I just can't get it to work without different errors.

It would be ideal if I could store that sum total into a variable so I can use it easily and probably subtract some value from it if necessary.

I use Repository and ViewModel to access my database and RecyclerView, Adapter and CardView to show different data in my MainActivity. I hope I don't need to use these to access this sum-number but get as direct access to it as possible.

This is my @entity

@Entity (tableName = "shift_table")
public class Shift implements Serializable {


@PrimaryKey(autoGenerate = true)
private int id;

@ColumnInfo(name = "date")
private String date;

@ColumnInfo(name = "start")
private String start;

@ColumnInfo(name = "end")
private String end;

@ColumnInfo (name = "hours")
private String hours;

@ColumnInfo (name = "wage")
private double wage;

public Shift(String date, String start, String end, String hours, double wage) {
    this.date = date;
    this.start = start;
    this.end = end;
    this.hours = hours;
    this.wage = wage;
}

public void setId(int id) {
    this.id = id;
}

public int getId() {
    return id;
}

public String getDate() {
    return date;
}

public String getStart() {
    return start;
}

public String getEnd() {
    return end;
}

public String getHours() {
    return hours;
}

public double getWage() {
    return wage;
}

}

This is my @Dao

@Dao
public interface ShiftDao {

@Insert
void insert (Shift shift);

@Update
void update (Shift shift);

@Delete
void delete (Shift shift);

@Query("DELETE FROM shift_table")
void deleteAllShifts();

@Query("SELECT * FROM shift_table ORDER BY id DESC")
LiveData<List<Shift>> getAllShifts();

@Query("SELECT SUM(wage) FROM shift_table")
What here? 
}

How I can get that SUM from wage-column to variable and show it in TextView?

@uneq95 For double type column value we can not be store correctly in the Long type. — Naresh Kumar, Jul 01 '20 at 09:22

score 1 · Answer 1 · answered Jan 15 '21 at 17:20

It should be something like this, in your DAO:

@Query("SELECT SUM(" + "wage" + ") as wagesSum "+ " FROM " + "shift_table")
double getWageSum();

Now, a few suggestions:

Put your column names into final constants, like this:

public final String COLUMN_DATE_NAME = "date"; @ColumnInfo(name = COLUMN_DATE_NAME) private String date;

This way, it will be easier for you to access the constants statically in DAO, and if in the future you might want to change the name of that column, you would change it only in the constant. Also, please do the same for the table name. These constants will help you when you write possible future migrations as well.

Happy coding!

ueen · Answer 2 · 2020-07-01T09:33:07.957

0

here is some of my kotlin code, enjoy :)

DAO

@Query("SELECT SUM(column) FROM table")
fun getSum(): Int

Repository

private val mDao: Dao

init {
    val database = getInstance(mApplication)    
    mDao = database.dao()
}


private class sumAsyncTask(private val mDao: Dao) : AsyncTask<Void, Void, Int>() {
    protected override fun doInBackground(vararg voids: Void): Int {
        return mDao.getSum()
    }

}

fun getSum(): Int {
    return sumAsyncTask(mDao).execute().get()
}

There might be other ways to do this async, this is what i use.

PS: as for the Doubles, you might want to store them as Long / SQL Integer instead (see Create Room Entity for a Table which has a field with LONG datatype in Sqlite) This can be done by simply multiplying by 100 (then you have cents if your wages represent money) before storing in the database

edited Jul 01 '20 at 09:33

answered Jul 01 '20 at 08:43

ueen

692
3
9
21

Thank you, for your answer! I couldn't get this to work. I get this error: "attempt to invoke virtual method on a null object reference" when I'm trying to access this getSum() method in my activity and get the result to a variable. How would you get this sum value from query to a variable or TextView in activity? – Luonnontieteilija Jul 01 '20 at 12:40
This might be an issue with your repository instance, heres a great tutorial series, that teaches how to connect all the dots (Room-Repository-ViewModel) https://www.youtube.com/watch?v=ARpn-1FPNE4 – ueen Jul 02 '20 at 09:10

score 0 · Answer 3 · answered Jul 01 '20 at 09:16

0

You can get sum of double column like below example. Sum of double column value will be store in BigDecimal. For more details you can check documentation.

@Query("SELECT SUM(column) FROM table")
LiveData<BigDecimal> getSum();

answered Jul 01 '20 at 09:16

Naresh Kumar

794
8
25

Trying to get sum from Room database column

3 Answers3