group-by

SELECT list is not in GROUP BY clause and contains nonaggregated column …. incompatible with sql_mode=only_full_group_by

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

How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?

PostgreSQL 9.0 or later: Modern Postgres (since 2010) has the string_agg(expression, delimiter) function which will do exactly what the asker was looking for: SELECT company_id, string_agg(employee, ‘, ‘) FROM mytable GROUP BY company_id; Postgres 9 also added the ability to specify an ORDER BY clause in any aggregate expression; otherwise you have to order all … Read more

How do I create a new column from the output of pandas groupby().sum()?

You want to use transform this will return a Series with the index aligned to the df so you can then add it as a new column: In [74]: df = pd.DataFrame({‘Date’: [‘2015-05-08’, ‘2015-05-07’, ‘2015-05-06’, ‘2015-05-05’, ‘2015-05-08’, ‘2015-05-07’, ‘2015-05-06’, ‘2015-05-05’], ‘Sym’: [‘aapl’, ‘aapl’, ‘aapl’, ‘aapl’, ‘aaww’, ‘aaww’, ‘aaww’, ‘aaww’], ‘Data2’: [11, 8, 10, 15, 110, … Read more

Group by in LINQ

Absolutely – you basically want: var results = from p in persons group p.car by p.PersonId into g select new { PersonId = g.Key, Cars = g.ToList() }; Or as a non-query expression: var results = persons.GroupBy( p => p.PersonId, p => p.car, (key, g) => new { PersonId = key, Cars = g.ToList() }); … Read more

Get statistics for each group (such as count, mean, etc) using pandas GroupBy?

Quick Answer: The simplest way to get row counts per group is by calling .size(), which returns a Series: df.groupby([‘col1′,’col2’]).size() Usually you want this result as a DataFrame (instead of a Series) so you can do: df.groupby([‘col1’, ‘col2’]).size().reset_index(name=”counts”) If you want to find out how to calculate the row counts and other statistics for each … Read more