Hi, I want to create a custom count, normally when...

# exposed

[Yoavya]

Hi,
I want to create a custom count, normally when I want to count a column I just columnX.count() in the slice part of the query.
I want to do something like this

count(*) filter ( where columnX in ('a', 'b') ) as custom_count

is there an easy way to achieve this?
Thanks 🙂

[Joel]

@Yoavya how would you write this in SQL? If you can show me the example query I can help you translate it for Exposed!

[Yoavya]

SELECT COUNT(*) as count, count(*) filter ( where answer.answer in ('yes', 'no') ) as count_having
FROM answer;

this is in Postgres

[Joel]

Ah, I am actually not familiar with that one. Is that a postgres thing?

[Yoavya]

I think so. yes

[Joel]

If it's unique to postgres then my guess is that it isn't supported by Exposed out of the box, probably need a custom function

[Joel]

I know you can do a correlated subquery using wrapAsExpression

[tapac]

@Yoavya,
I'm not sure what it will work, but you could try:

fun CountWithFilter(condition: SqlExpressionBuilder.() -> Op<Boolean>) = 
    CustomLongFunction("count(*) filter", SqlExpressionBuilder.condition())

fun main() {
    val countFilter = CountWithFilter { AnswerTable.answer inList listOf("yes", "no") }
AnserTable.slice(countFilter).selectAll()
}

[Yoavya]

thanks 🙂

[Joel]

This is pretty neat! Wonder if it works for other aggregations...