Exposed: Columns sum

[kszymanski85]

Hey,

Maybe it has been already addressed and working in some way but searched doc, githun, stackoerflow and googled without results. The issue is that I didn't wound solution to add two rows values in different columns in the same table. Something like:
"select a, sum(b,c) as sth from sth_table".

If someone know how do do this appreciate any help.

Regards
Kris

[Tapac]

If both columns are numbers you could try:

val sum = Expression.build { YourTable.b + YourTable.c }
YourTable.slice(YourTable.a, sum).selectAll().map { it[YourTable.a] to it[sum] }

[kszymanski85]

Thank you for quick resply. The solution work great but would like to ask about one more thing regarding to this. The table is setup in way (I'm not able to force change that) where one of sum() parameter can be null in db. Is there any simple way to manage .bulid() construction.

where/if kotlin statement with checking .isNull()/.isNotNull return Column. Don't see posibiluty to caste it by property function to Boolean. Event if caste I need something like Column(0) or other to get correct sum() result in case if one of table column is null.

[kszymanski85]

Just in case if someone has similar issue.
Resolved it by:

1. add aggreation column in slice(), for example userId
2. sum null and non-null column
   val sum = Expression.build{ SomeTable.field.sum() + SomeTable.field.sum() }
3. group by aggregation colum from pt.1 after select()/selectAll()

@Tapac
Great thanks for kicking me forward after get stuck in columns sum().

[Tapac]

@kszymanski85 , when you need to replace null value with something meaningful use coalesce function:

        val sum = Expression.build { 
            val exp1 = coalesce(SomeTable.field, intLiteral(0))
            val exp2 = coalesce(SomeTable.field, intLiteral(0))
            exp1 + exp2
        }