Home > Software engineering >  SELECT DISTINCT values grouping for one columns in Google Sheets Query
SELECT DISTINCT values grouping for one columns in Google Sheets Query

Time:09-25

Let´s say I have this set of dummy data in a .gsheet spreadsheet which Sheet is called Sheet11:

enter image description here

I need to get the following output:

enter image description here

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:

enter image description here

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")

enter image description here

  • Related