Friday, January 6, 2017

yogi_Get Unique List Of Entities And Their Respective Count From A Single Column List Of Entities

Google Spreadsheet   Post  #2105
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-06-2017
question by: jschram:
https://productforums.google.com/forum/#!topic/docs/9HfIjoM9cU8;context-place=forum/docs
QUERY to Aggregate and Group By a single column
I'm surprised by the behaviour of the QUERY function if I have the below sample data...

Names
--------
Andy
Susan
James
Janice
Susan
Larry
Janice
Susan
Andy

With the formula: 

QUERY(A1:A10,"select A, count(A) group by A", 1) 

And I would expect to produce a result similar to a pivot table with the columns Distinct(A) and Count(A) however the formula fails to parse. I have read the QUERY documentation and I didn't find anything that specifically forbids multiple uses of the same Column Id in a query. I was playing with some ideas on this sheet but I'm open to any advice,