This will do what you want (list of towns, with the number of users in each): SELECT `town`, COUNT(`town`) FROM `user` GROUP BY `town`; You can use most aggregate functions when using a GROUP BY statement (COUNT, MAX, COUNT DISTINCT etc.) Update: You can declare a variable for the number of users and save the … Read more
Using an ORDER BY in a subquery is not the best solution to this problem. The best solution to get the max(post_date) by author is to use a subquery to return the max date and then join that to your table on both the post_author and the max date. The solution should be: SELECT p1.* … Read more
After calling GroupBy, you get a series of groups IEnumerable<Grouping>, where each Grouping itself exposes the Key used to create the group and also is an IEnumerable<T> of whatever items are in your original data set. You just have to call Count() on that Grouping to get the subtotal. foreach(var line in data.GroupBy(info => info.metric) … Read more
This would be the easier way to do it using aggregate: db.contest.aggregate([ {“$group” : {_id:”$province”, count:{$sum:1}}} ])
They are essentially equivalent to each other (in fact this is how some databases implement DISTINCT under the hood). If one of them is faster, it’s going to be DISTINCT. This is because, although the two are the same, a query optimizer would have to catch the fact that your GROUP BY is not taking … Read more
Suppose I have the following table T: a b ——– 1 abc 1 def 1 ghi 2 jkl 2 mno 2 pqr And I do the following query: SELECT a, b FROM T GROUP BY a The output should have two rows, one row where a=1 and a second row where a=2. But what should … Read more
This Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘returntr_prod.tbl_customer_pod_uploads.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by will be simply solved by changing the sql mode in MySQL by this command, SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,’ONLY_FULL_GROUP_BY’,”)); This too works for … Read more
It means to group by the first column of your result set regardless of what it’s called. You can do the same with ORDER BY.
The second half of the currently accepted answer is outdated and has two deprecations. First and most important, you can no longer pass a dictionary of dictionaries to the agg groupby method. Second, never use .ix. If you desire to work with two separate columns at the same time I would suggest using the apply … Read more
Use GroupBy.sum: df.groupby([‘Fruit’,’Name’]).sum() Out[31]: Number Fruit Name Apples Bob 16 Mike 9 Steve 10 Grapes Bob 35 Tom 87 Tony 15 Oranges Bob 67 Mike 57 Tom 15 Tony 1 To specify the column to sum, use this: df.groupby([‘Name’, ‘Fruit’])[‘Number’].sum()