Let´s say I have this set of dummy data in a .gsheet spreadsheet which Sheet is called Sheet11:
I need to get the following output:
which means that I´d be selecting
column A corresponding to the projects, counting
the users in column B where
the Billable is equals TRUE, and then grouping
by A and last ordering
by the column counted B.
Applying the query formula =query(Sheet11!A1:C13,"select A,count(B) where C=TRUE group by A order by count(B) desc",1)
, I am getting this:
It counts all the users assigned to project1 and all assigned to project2, I´d need to get the count of unique users for both projects.
How could I apply some kind of unique like in SQL?
CodePudding user response:
You can use subselect:
=query(query(A1:C13,"select A,B, count(B) where C=TRUE group by A,B",1),"SELECT Col1, COUNT(Col2) GROUP BY Col1 ORDER BY COUNT(Col2) desc")