Question: How can I do inner join queries on the same table with SQL aliasses (AS keyword) in Anko-SQLite?

[SeppPenner]

I have the following table definition in SQLITE:

CREATE TABLE FuelData (
Id INT(10) AUTO_INCREMENT PRIMARY KEY,
TimestampDate VARCHAR(255),
MonthYear INT(6),
YearA INT(4),
Price DOUBLE,
Mileage INT(10),
Fueled DOUBLE
);

How can I perform the following queries (Inner Join on 2 times the same table) in Anko?

#Select for liters per 100 km and date
SELECT FuelData1.Id, FuelData2.TimestampDate, (FuelData2.Fueled / (FuelData2.Mileage - FuelData1.Mileage) * 100) AS LitersPer100Km
FROM FuelData AS FuelData1
INNER JOIN FuelData AS FuelData2
On FuelData1.Id = (FuelData2.Id - 1);

#Select for costs on monthly basis
SELECT SUM(FuelData.Fueled * FuelData.Price) AS PricePerMonth, FuelData.MonthYear
FROM FuelData
GROUP BY FuelData.MonthYear;

#Select for costs on yearly basis
SELECT SUM(FuelData.Fueled * FuelData.Price) AS PricePerYear, FuelData.YearA
FROM FuelData
GROUP BY FuelData.YearA;

I've tried the following:

private fun readFuelData2(): List<FuelData2>{
        var resultList = ArrayList<FuelData2>()
        val rowParser = classParser<FuelData2>()
        database.use {
            select("FuelData AS FuelData1 INNER JOIN FuelData AS FuelData2 On FuelData1.Id = (FuelData2.Id - 1)",
                    "FuelData1.id, FuelData2.currentDate, (FuelData2.fueled / (FuelData2.mileage - FuelData1.mileage) * 100)" +
                            " AS litersPer100Km")
                    .parseList(rowParser).forEach {
                        fuelData -> resultList.add(fuelData)
                    }
        }
        return resultList
    }

class FuelData2(
        val id : Int,
        val currentDate: String,
        val litersPer100Km: Double
)

as described here: https://stackoverflow.com/questions/45912680/select-data-from-two-tables-in-kotlin-anko but this does not work, too.

Can this be done via a raw query or something like that?

[SeppPenner]

Ok, it's quite easy if you know how to get a raw query in Anko-SQLite...:

Using the following description: http://androidopentutorials.com/android-sqlite-join-multiple-tables-example/ I managed to do the following:

I added a data class:

class FuelData2(
        val id : Int,
        val currentDate: String,
        val litersPer100Km: Double
)

and added the logic to my activity class:

private fun readFuelData2(): ArrayList<FuelData2> {
        val resultList = ArrayList<FuelData2>()
        val query = "SELECT FuelData1.Id, FuelData2.CurrentDate, (FuelData2.Fueled / (FuelData2.Mileage - FuelData1.Mileage) * 100) AS LitersPer100Km" +
                " FROM FuelData AS FuelData1" +
                " INNER JOIN FuelData AS FuelData2" +
                " ON FuelData1.Id = (FuelData2.Id - 1);"
        database.use {
            val cursor = database.writableDatabase.rawQuery(query, null)
            while (cursor.moveToNext()) {
                val fuelData = FuelData2(id = cursor.getInt(0),currentDate = cursor.getString(1),
                        litersPer100Km = cursor.getDouble(2))
                resultList.add(fuelData)
            }
        }
        return resultList
    }