Thursday, June 27, 2013

Google Spreadsheet Group By Query

Question:

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

user       *        1
JAY                 1
JOE       *         1
JOHN               1
JAY                  
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


Solution:

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: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

No comments:

Post a Comment