How to calculate row of one table based on data of another table in room databse

Suppose there is a restraunts with the following tables -

@Entity(tablename = "pizza-sales")
data class Pizza(  
  val id,
  val name,
  val price,
  val quantitySold,
  val date,
) //Pizza sales of a day

@Entity(tablename = "burger-sales")
data class Burger(
  val id,
  val name,
  val price,
  val quantitySold,
  val date,
) //Burger sales of a day

Now, I want a third table(total sales), where the rows are calculated from the above tables

@Entity(tablename= "total-sales")
data class TotalSales(
  val id,
  val date,
  val totalQuantity = Sum(Pizza[quantitySold]) + Sum(Burger[quantitySold]),
  val totalPrice = Sum(Pizza[quantitySold * price]) + Sum(Burger[quantitySold * price])
) //Total sales of a day

Note that I am Summing over Pizza,Burger where TotalSales.date = (Burger/Pizza).date.

Also, is it possible to do the arithmetic inside queries like this-

@Dao
interface ToatalSalesDao {
    @Query(
        "ADD pizza-sales.quantitySold WHERE pizza-sales.date = :date"+
        "ADD"+
        "ADD burger-sales.quantitySold WHERE burger-sales.date = :date"
        )
    fun loadTotalSalesQuantityOfTheDay(date: String): Int
}

Also, is it possible to do the arithmetic inside queries like this-

yes, BUT ADD is not valid SQL, so:-

Perhaps consider the following query:-

WITH cte_combine AS (SELECT * FROM `pizza-sales` UNION ALL SELECT * FROM `burger-sales`)
INSERT OR REPLACE INTO `total-sales`
    SELECT date,sum(quantitysold),sum(price * quantitysold) FROM cte_combine GROUP BY date
;

• Note! the GROUP BY clause assumes that the date is in a format that is sortable without manipulation (the example uses dates in the format YYYY-MM-DD)

To demonstrate, using an SQLite tool, consider this native SQLite (based upon the @Entity annotated classes in the question)

• Note that column types have been assumed

:-

DROP TABLE IF EXISTS `pizza-sales`;
DROP TABLE IF EXISTS `burger-sales`;
DROP TABLE IF EXISTS `total-sales`;
CREATE TABLE IF NOT EXISTS `pizza-sales` (id INTEGER PRIMARY KEY, name TEXT, price REAL, quantitySold INTEGER, date TEXT);
CREATE TABLE IF NOT EXISTS `burger-sales` (id INTEGER PRIMARY KEY, name TEXT, price REAL, quantitySold INTEGER, date TEXT);
CREATE TABLE IF NOT EXISTS `total-sales` (/*id INTEGER PRIMARY KEY !!!!!!!!!!probably not required!!!!!!!!!!,*/ date TEXT PRIMARY KEY, totalQuantity INTEGER, totalPrice REAL);

/* Add some pizza data */
INSERT INTO `pizza-sales` (name,price,quantitySold,date) VALUES
    ('Hawian',3.50,3,'2024-03-14')
    ,('Meat Lovers',3.50,2,'2024-03-14')
    ,('Plain',3.50,5,'2024-03-14')
    ,('Hawian',3.50,13,'2024-03-15')
    ,('Meat Lovers',3.50,12,'2024-03-15')
    ,('Plain',3.50,15,'2024-03-15') 
;
/* add some burger data */
INSERT INTO `burger-sales` (name,price,quantitySold,date) VALUES
    ('Big Whatever',3.50,3,'2024-03-14')
    ,('Huge Thinggy',3.50,2,'2024-03-14')
    ,('Cheese',3.50,5,'2024-03-14')
    , ('Big Whatever',3.50,10,'2024-03-15')
    ,('Huge Thinggy',3.50,11,'2024-03-15')
    ,('Cheese',3.50,12,'2024-03-15')
;

SELECT * FROM `pizza-sales`;
SELECT * FROM `burger-sales`;
/* An INSERT SELECT that utilised a CTE (Common table expression) which combines the data from the pizza and burger table
    the INSERT is then driven by the calculated values using the sum function, the summing being based upon the GROUP BY clause
   Duplicate data, according to date, would be replaced
*/
WITH cte_combine AS (SELECT * FROM `pizza-sales` UNION ALL SELECT * FROM `burger-sales`)
INSERT OR REPLACE INTO `total-sales`
    SELECT date,sum(quantitysold),sum(price * quantitysold) FROM cte_combine GROUP BY date
;
SELECT * FROM `total-sales`;

/* Cleanup Demo */

DROP TABLE IF EXISTS `pizza-sales`;
DROP TABLE IF EXISTS `burger-sales`;
DROP TABLE IF EXISTS `total-sales`;

The output from the 3 SELECTs being:-

• Pizza-sales

• burger-sales

Finally the total-sales table:-

Perhaps note the comments included in the SQL

---

Room

---

Converting the above to Room based upon your code (corrected and types assumed) so the @Entity annotated classes are:-

@Entity(tableName = "pizza-sales")
data class Pizza(
    @PrimaryKey
    val id: Long?=null,
    val name: String,
    val price: Double,
    val quantitySold: Int,
    val date: String
) //Pizza sales of a day

@Entity(tableName = "burger-sales")
data class Burger(
    @PrimaryKey
    val id: Long?=null,
    val name: String,
    val price: Double,
    val quantitySold: Int,
    val date: String,
) //Burger sales of a day

@Entity(tableName= "total-sales")
data class TotalSales(
    /*val id, probably not required */
    @PrimaryKey
    val date: String,
    val totalQuantity: Int /*= Sum(Pizza[quantitySold]) + Sum(Burger[quantitySold])*/,
    val totalPrice: Double  /* = Sum(Pizza[quantitySold * price]) + Sum(Burger[quantitySold * price])*/
) //Total sales of a day

With the following functions in an @Dao annotated interface (named TheDAOs):-

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(pizza: Pizza): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(burger: Burger): Long

@Query("WITH cte_combine AS " +
        "    (SELECT * FROM `pizza-sales` UNION ALL SELECT * FROM `burger-sales`) " +
        "INSERT OR REPLACE INTO `total-sales` SELECT date,sum(quantitysold),sum(price * quantitysold) FROM cte_combine GROUP BY date" +
        ";")
fun generateTotals();

@Query("SELECT * FROM `total-sales`")
fun getAllTotals(): List<TotalSales>

And with the following activity code (note that for the demo's brevity, the main thread has been utilised):-

    db = TheDatabase.getInstance(this)
    dao = db.getTheDAOs()

    dao.insert(Pizza(name = "Hawian", price = 3.50, quantitySold = 3, date = "2024-03-14"))
    dao.insert(Pizza(name = "Meat Lovers", price = 3.50, quantitySold = 2, date = "2024-03-14"))
    dao.insert(Pizza(name = "Plain", price = 3.50, quantitySold = 5, date = "2024-03-14"))
    dao.insert(Pizza(name = "Hawian", price = 3.50, quantitySold = 13, date = "2024-03-15"))
    dao.insert(Pizza(name = "Meat Lovers", price = 3.50, quantitySold = 12, date = "2024-03-15"))
    dao.insert(Pizza(name = "Plain", price = 3.50, quantitySold = 15, date = "2024-03-15"))

    dao.insert(Burger(name = "Big Whatever", price = 3.50, quantitySold =  3, date ="2024-03-14"))
    dao.insert(Burger(name = "Huge Thinggy", price = 3.50, quantitySold =  2, date ="2024-03-14"))
    dao.insert(Burger(name = "Cheese", price = 3.50, quantitySold =  5, date ="2024-03-14"))
    dao.insert(Burger(name = "Big Whatever", price = 3.50, quantitySold =  10, date ="2024-03-15"))
    dao.insert(Burger(name = "Huge Thinggy", price = 3.50, quantitySold =  11, date ="2024-03-15"))
    dao.insert(Burger(name = "Cheese", price = 3.50, quantitySold =  12, date ="2024-03-15"))

    dao.generateTotals()
    for (ts in dao.getAllTotals()) {
        Log.d("DBINFO_1","TS Date=${ts.date} TOTQTY=${ts.totalQuantity} TOTPRICE=${ts.totalPrice}")
    }
    /* do again to show that REPLACE does not affect the end result (but would if prices or quintities changed) */
    dao.generateTotals()
    for (ts in dao.getAllTotals()) {
        Log.d("DBINFO_1","TS Date=${ts.date} TOTQTY=${ts.totalQuantity} TOTPRICE=${ts.totalPrice}")
    }

The output to the log being:-

2024-10-14 19:11:34.902 D/DBINFO_1: TS Date=2024-03-14 TOTQTY=20 TOTPRICE=70.0
2024-10-14 19:11:34.902 D/DBINFO_1: TS Date=2024-03-15 TOTQTY=73 TOTPRICE=255.5
2024-10-14 19:11:34.904 D/DBINFO_1: TS Date=2024-03-14 TOTQTY=20 TOTPRICE=70.0
2024-10-14 19:11:34.904 D/DBINFO_1: TS Date=2024-03-15 TOTQTY=73 TOTPRICE=255.5

Using App Inspection then the total-sales table is:-

Note! the design could very likely be improved but that is beyond the scope of the question/answer

• such as having a single table for all sale types with a column to denote the type (probably related to a table for the types).