New answer using Mongo aggregation framework After this question was asked and answered, 10gen released Mongodb version 2.2 with an aggregation framework. The new best way to do this query is: db.col.aggregate( [ { $group: { _id: { userId: “$userId”, name: “$name” }, count: { $sum: 1 } } }, { $match: { count: { … Read more
You can only use aggregates for comparison in the HAVING clause: GROUP BY … HAVING SUM(cash) > 500 The HAVING clause requires you to define a GROUP BY clause. To get the first row where the sum of all the previous cash is greater than a certain value, use: SELECT y.id, y.cash FROM (SELECT t.id, … Read more
Why is it that you need to place columns you create yourself (for example “select 1 as number”) after HAVING and not WHERE in MySQL? WHERE is applied before GROUP BY, HAVING is applied after (and can filter on aggregates). In general, you can reference aliases in neither of these clauses, but MySQL allows referencing … Read more
You could use a HAVING clause, which can see the aliases, e.g. HAVING avg_rating>5 but in a where clause you’ll need to repeat your expression, e.g. WHERE (sum(reviews.rev_rating)/count(reviews.rev_id))>5 BUT! Not all expressions will be allowed – using an aggregating function like SUM will not work, in which case you’ll need to use a HAVING clause. … Read more