Grouping

GroupBy

Use a sql.select function to do groupBy. You can use the groupBy function to group by multiple columns.

val q: SqlQuery<Pair<Person, Address>> =
  sql.select {
    val p = from(Table<Person>())
    val a = join(Table<Address>()) { a -> a.ownerId == p.id }
    groupBy(p.name, a.street)
    MyData(p.name, a.street, avg(p.age)) // Average age of all people named X on the same street
  }
//> SELECT p.name, a.street, avg(p.age) FROM Person p

Having

You can use the having function to filter the results of a groupBy query.

val q: SqlQuery<Pair<Person, Address>> =
  sql.select {
    val p = from(Table<Person>())
    val a = join(Table<Address>()) { a -> a.ownerId == p.id }
    groupBy(p.name, a.street)
    having { avg(p.age) > 18 }
    MyData(p.name, a.street, avg(p.age)) // Average age of all people named X on the same street
  }
//> SELECT p.name, a.street, avg(p.age) FROM Person p
//  JOIN Address a ON a.personId = p.id
//  GROUP BY p.name, a.street
//  HAVING avg(p.age) > 18