Thursday, June 27, 2013

Google Spreadsheet Group By Query


I have a spread sheet that has multiple entries for each user

user       *        1
JAY                 1
JOE       *         1
JOHN               1
JOHN     *         1
JOE       *          
STEVE  *         

Now I have two queries, one that counts the number of * in column 2 grouped by user and one that counts the number of 1's in column 3 grouped by user.  Is there a way to put both counts in the same query so you can build a table like:

user         *        1
JAY         0        1
JOE         2        1
JOHN       1        2
STEVE     1        0

If I try with the two queries I get:
JOE      2  JAY     1
JOHN   1   JOE     1
STEVE 1   JOHN   2

And that does not look good


Have a look at the screenshot below:

I have entered the data in Column A, B and C.

And then I have inserted the following formulas:
In Cell E1:
=query(A2:C;"select A,count(B),count(C) where A<>'' group by A label A'User',count(B) '*', count(C) '1'")

In Cell E7:
=query(arrayformula(if({1,0,0};A2:A;if({0,1,0};if(B2:B="*";1;"");C2:C)));"select Col1,count(Col2),count(Col3) where Col1<>'' group by Col1 label Col1'User',count(Col2) '*', count(Col3) '1'")

Formula in Cell E1 was not counting stars that is * , so I coded a new formula which is in Cell E7.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: 


1 comment:

  1. It is interesting to note how global the world was as far back as 1921. During the course of the history of the teddy bear, The J.K.Farnell & Co. manufactured bears in England. And, it was one of Farnell's teddy bears that Christopher Robin received as a gift from his mother for his first birthday in 1921. Christopher Robin Milne is A.A. Milne's son and it was Christopher's adventures with his teddy bear [he named him Edward Bear] and other stuffed animals, that inspired A.A. Milne to write stories about a boy, a bear, and other stuffed toys. So, where did the name, Winnie-the-Pooh, come from then? popular teddy bears