search

rethinkdb / docs

RethinkDB documentation
http://rethinkdb.com/docs
Apache License 2.0
117 stars 167 forks source link

SQL->reql cheatsheet: add example of group by having count #757

Closed rocketraman closed 9 years ago

rocketraman commented 9 years ago

An example of an SQL with a group by having count(*) > x would be useful. For example, in the movies database, if one wanted to find the duplicate movies (not distinct).

SELECT title,
       COUNT(title)
    FROM movies
    GROUP BY title
    HAVING COUNT(title) > 1
r.table('movies').withFields('title').group(r.row).count().ungroup()
 .filter(r.row('reduction').gt(1))
danielmewes commented 9 years ago

Thanks for the suggestion @rocketraman . That sounds useful, especially because it demonstrates the use of ungroup() which might not be obvious.

I think the query should be

r.table('movies').group('title').count().ungroup()
 .filter(r.row('reduction').gt(1))

right?

rocketraman commented 9 years ago

@danielmewes Yes I think your query is simpler, and the result shape is closer to the shape of the SQL result. Cool!