Get complex response from Android Room with sqlite query

Question

Based on response to my question here

I have simple table:

id, name, city

and I created a class for it:

@Entity(tableName = "table2")
data class Table2 @JvmOverloads constructor(
  @PrimaryKey @ColumnInfo(name = "id") val id: Int? = 0,
  @ColumnInfo(name = "name") val name: String? = "",
  @ColumnInfo(name = "city") val city: String? = ""
)

I entered next rows:

1 John London
2 Mary Paris
3 John Paris
4 Samy London

I want to get such a result:

      London  Paris
John    1       1
Mary    0       1
Samy    1       0
Total   2       2

It's not important to get Total row together with John, Mary and Samy lines, now I just want to get a simple response without complex query.

Since I want to get response list of classes containing 3 fields:

city, Int, Int

I created a class for it:

data class CityName constructor(
    val name: String? = "",
    val countLondon: Int? = 0,
    val countParis: Int? = 0
)

Next I tried to create a SQLite query to get a result from Android Room.

@Query("SELECT name, 
    COUNT(CASE WHEN city = :london THEN 1 ELSE 0 END) 'London', 
    COUNT(CASE WHEN city = :paris THEN 1 ELSE 0 END) 'Paris' 
    FROM table2 GROUP BY name")
fun getPivot_CityNameList(london: String, paris: String): List<CityName>

I excluded/included from the query 'London' and 'Paris', I swapped some words etc. but I always get a result like this:

John, countLondon: null, countParis: null
Mary, countLondon: null, countParis: null
Samy, countLondon: null, countParis: null

It's easy to get subset from a table, like city and id or to get only a count of some field but I have no idea how to combine subset (name) with two counts in one response.

What is wrong: my query or CityName class response? How to fix it?

ANSWER

The correct query is

SELECT name,
COUNT(CASE WHEN city = :london THEN 1 END) as countLondon, 
COUNT(CASE WHEN city = :paris THEN 1 END) as countParis 
FROM table2 GROUP BY name

as mentioned in the accepted answer I missed as countLondon but also I deleted ELSE 0 from CASE statement

Answer 1

Your CityName fields' names should match your query's columns' aliases. That's how Room then copies values from SQLite cursor to fields of this class.

So change aliases for columns, and your code should work:

@Query("SELECT name, 
    COUNT(CASE WHEN city = :london THEN 1 ELSE 0 END) as countLondon, // <- change here 
    COUNT(CASE WHEN city = :paris THEN 1 ELSE 0 END) as countParis // <- change here
    FROM table2 GROUP BY name")
fun getPivot_CityNameList(london: String, paris: String): List<CityName>