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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

Kishan.

iGoogleDrive