Kotlin Exposed Sum on condition (CaseWhenElse)

Question

I have a table with data structure like :

id    type   cityName   regDate
1249     0   City1      2019-10-01

I want to get result output of unique cities and the number of registrations in it per month as a list of data class objects of

data class NewClientsNumberCityMonth(
    val cityName: String = "",
    val januaryNewClientsNumber :Int = 0,
    val februaryNewClientsNumber :Int = 0,
    val marchNewClientsNumber :Int = 0,
    val aprilNewClientsNumber :Int = 0,
    val mayNewClientsNumber :Int = 0,
    val juneNewClientsNumber :Int = 0,
    val julyNewClientsNumber :Int = 0,
    val augustNewClientsNumber :Int = 0,
    val septemberNewClientsNumber :Int = 0,
    val octoberNewClientsNumber :Int = 0,
    val novemberNewClientsNumber :Int = 0,
    val decemberNewClientsNumber :Int = 0
    val total :Int = 0
)

and use this objects as the strings to fill the table (we dont know the number of unique cities), result should be like :

City1     5    8    3    1    2    1    4    1    2    1    0    0 
City2    69   23    7    5    3   10   24   14   12   23   25   10
...

im trying this

val tempMutList = mutableListOf<NewClientsNumberCityMonthModel>()

transaction(Connection.TRANSACTION_SERIALIZABLE, 2) {
                        addLogger(StdOutSqlLogger)


                        ClientsDataExposed
                            .slice(ClientsDataExposed.cityName)
                            .selectAll()
                            .groupBy(ClientsDataExposed.cityName)
                            .map { it[ClientsDataExposed.cityName] }
                            .toList().forEach {

                              val regsInCity = ClientsDataExposed
                                .slice(
                                        ClientsDataExposed.id,
                                        ClientsDataExposed.cityName,
                                        ClientsDataExposed.type,
                                        ClientsDataExposed.regDate,
                                        ClientsDataExposed.regDate.month()

                                    )
                                    .selectAll()
                                    .andWhere { ClientsDataExposed.cityName.eq(it) }
                                    .andWhere {
                                        ClientsDataExposed.regDate.between(
                                            Date.valueOf("2019-01-01".toString()),
                                            Date.valueOf("2020-01-01".toString())
                                        )
                                    }
                                    .andWhere {
                                        (ClientsDataExposed.type.inList(contracterTypeSelectedCheckboxes.filterValues { it }.keys.toList())) and (ClientsDataExposed.cityName.inList(
                                            citiesSelectedChekboxes.filterValues { it }.keys.toList()
                                        ))
                                    }
                                    .map { it[ClientsDataExposed.regDate.month()] }
                                    .toList()

                        val cityClientsPerMonth = NewClientsNumberCityMonthModel(
                            it,
                            regsInCity.count { it == 1 },
                            regsInCity.count { it == 2 },
                            regsInCity.count { it == 3 },
                            regsInCity.count { it == 4 },
                            regsInCity.count { it == 5 },
                            regsInCity.count { it == 6 },
                            regsInCity.count { it == 7 },
                            regsInCity.count { it == 8 },
                            regsInCity.count { it == 9 },
                            regsInCity.count { it == 10 },
                            regsInCity.count { it == 11 },
                            regsInCity.count { it == 12 },
                            regsInCity.count()
                            )
                               tempMutList.add(cityClientsPerMonth)    

                                //obj of dataclass
                            }

               viewTableTabOfnewClientsNumberCityMonth.value = tempMutList.map { it }
}

i know, that i should use Sum() and CaseWhenElse , like here , with check on ClientsDataExposed.regDate.month() which gives a month number (1-12), to assign the sum() result to my data class property, but i can't find any examples of CaseWhenElse syntax and i can't figure out it myself, or may be there is another way of getting it whithout using of CaseWhenElse ?

Answer 1

Please check that code:

    val monthExpr = ClientsDataExposed.regDate.month()

    fun sumByMonth(monthNum: Int) = Expression.build { 
        val caseExpr = case().
            When(monthExpr eq intLiteral(monthNum), intLiteral(1)).
            Else(intLiteral(0))
        Sum(caseExpr, IntegerColumnType())
    }

    val janSumExp = sumByMonth(1)
    val febSumExp = sumByMonth(2)
    ClientsDataExposed.
        slice(ClientsDataExposed.cityName, janSumExp, febSumExp).
        selectAll().
        groupBy(ClientsDataExposed.id).map { 
          val janSum = it[janSumExp]
          val febSum = it[febSumExp]
          ...
        }