group-by

How to do a case sensitive GROUP BY?

by

You need to cast the text as binary (or use a case-sensitive collation). With temp as ( select ‘Test’ as name UNION ALL select ‘TEST’ UNION ALL select ‘test’ UNION ALL select ‘tester’ UNION ALL select ‘tester’ ) Select Name, COUNT(name) From temp Group By Name, Cast(name As varbinary(100)) Using a collation: Select Name Collate … Read more

How to group by a Calculated Field

by

Sure, just add the same calculation to the GROUP BY clause: select dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 – datepart(weekday, mwspp.DateDue))), sum(mwspp.QtyRequired) from manufacturingweekshortagepartpurchasing mwspp where mwspp.buildScheduleSimID = 10109 and mwspp.partID = 8366 group by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 – datepart(weekday, mwspp.DateDue))) order by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 – datepart(weekday, mwspp.DateDue))) … Read more

How to get number of groups in a groupby object in pandas?

by

Simple, Fast, and Pandaic: ngroups Newer versions of the groupby API (pandas >= 0.23) provide this (undocumented) attribute which stores the number of groups in a GroupBy object. # setup df = pd.DataFrame({‘A’: list(‘aabbcccd’)}) dfg = df.groupby(‘A’) # call `.ngroups` on the GroupBy object dfg.ngroups # 4 Note that this is different from GroupBy.groups which … Read more

Difference between groupby and pivot_table for pandas dataframes

by

Both pivot_table and groupby are used to aggregate your dataframe. The difference is only with regard to the shape of the result. Using pd.pivot_table(df, index=[“a”], columns=[“b”], values=[“c”], aggfunc=np.sum) a table is created where a is on the row axis, b is on the column axis, and the values are the sum of c. Example: df … Read more

Reusable function to group_by but return an object with group as key

by

Here’s a variant using reduce instead of group_by: reduce .[] as $m ({}; .[$m.country] += [$m]) Demo Or as a defined function: def grp(f): reduce .[] as $m ({}; .[$m|f] += [$m]); grp(.country) Demo { “germany”: [ { “name”: “anna”, “country”: “germany” }, { “name”: “lisa”, “country”: “germany” } ], “usa”: [ { “name”: “john”, … Read more