Re: query count with group by

[Erich S]

Sorry to waste your bandwidth: the query works as expected once I replaced the curly braces with round braces.

Cheers, Erich

On Tuesday, November 6, 2012 10:20:43 AM UTC+1, Erich S wrote:

Dear all,

I am struggling with a query to produce SQL like:

select language,count(*) from book group by language

I have been looking at the SchoolDb etc examples on GitHub, but the queries given there always involve some kind of join.
What I have tried so far:

case class Book(id: Long, title: String, language: String) extends KeyedEntity[Long] {}

object Book {
...
def totalCountQ: Query[Measures[Long]] = from(bookTable) { // this works fine and gives the total count of Books in the DB
        book =>
            compute(count)
}

def countByLangQ = from(bookTable) {
        book =>
            select(book.language, compute(count))
            groupBy(book.language)
}

The latter query results in SQL like:

Select
  "booxx_book_edition1"."language" as "g0"
From
  "booxx_book_edition" "booxx_book_edition1"
Group By
  "booxx_book_edition1"."language"

Effectively, I get sth similar to "select distinct language from book". But where are my counts?

I have also tried:

def countByLangQ = from(bookTable) {
        book =>
            groupBy(book.language)
            compute(count)
    }

but this simply will give the total count:

Select
  count(*) as "c0"
From
  "booxx_book_edition" "booxx_book_edition1"

Any help or pointers to working examples are highly appreciated.

Regards, Erich

[Maxime Lévesque]

Was about to reply ... The curly braces vs parenthesis is one of Scala's gotcha.
Fortunately one only need to live through it once before it gets engraved in
one's mental bios !

2012/11/6 Erich S <ewsch...@googlemail.com>: