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
try this; select gid from `gd` group by gid having count(*) > 10 order by lastupdated desc
SELECT a.license_id, a.limit_call , count(b.license_id) AS overall_count FROM “License” a LEFT JOIN “Log” b USING (license_id) WHERE a.license_id = 7 GROUP BY a.license_id — , a.limit_call — add in old versions HAVING a.limit_call > count(b.license_id) Since Postgres 9.1 the primary key covers all columns of a table in the GROUP BY clause. In older versions … Read more
If there aren’t any unique constraints try: SELECT post_id FROM tags WHERE tag_id = 1 OR tag_id = 3 GROUP BY post_id HAVING count(DISTINCT tag_id) = 2; Or use this HAVING clause, if trying to detect only two tag_id values: HAVING MIN(tag_id) <> MAX(tag_id) If post_id and tag_id both have an unique constraint, this should … Read more
Use the HAVING clause and GROUP By the fields that make the row unique The below will find all users that have more than one payment per day with the same account number SELECT user_id , COUNT(*) count FROM PAYMENT GROUP BY account, user_id , date HAVING COUNT(*) > 1 Update If you want to … Read more
Like this: from c in db.Company group c by c.Name into grp where grp.Count() > 1 select grp.Key Or, using the method syntax: Company .GroupBy(c => c.Name) .Where(grp => grp.Count() > 1) .Select(grp => grp.Key);
HAVING: is used to check conditions after the aggregation takes place. WHERE: is used to check conditions before the aggregation takes place. This code: select City, CNT=Count(1) From Address Where State=”MA” Group By City Gives you a table of all cities in MA and the number of addresses in each city. This code: select City, … 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
WHERE clause introduces a condition on individual rows; HAVING clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows. Your query calls for a second kind of condition (i.e. a condition on an aggregation) hence HAVING works … Read more