How to return DISTINCT/UNIQUE list of text via Google QUERY?

Question

Given the list of categories and sub-categories:

CatA    SubCatA
CatA    SubCatA
CatB    SubCatB
CatB    SubCatB
CatC    SubCatC
CatC    SubCatC

I'd like to return all unique main categories based on the secondary category.

I don't want to use:

=QUERY(A1:B6,"SELECT A WHERE B = 'SubCatA'")

as it's returning the list with duplicates (returning first element won't help either).

What I'm trying is:

=QUERY(A1:B6,"SELECT A WHERE B = 'SubCatA' GROUP BY A")

but it gives me the error: CANNOT_GROUP_WITHOUT_AGG, therefore I'm trying to find something equivalent to either DISTINCT(A) or UNIQUE(A), but these functions doesn't exist.

Is there any function in Google Visualization API Query Language equivalent to DISTINCT/UNIQUE to return list of unique values?

Answer 1

Without a query:

=unique(filter(A1:A6, B1:B6="SubCatA"))

Answer 2

In addition to using the ever-useful UNIQUE function around FILTER, as user79865 suggests, you can also use UNIQUE around the original QUERY function (which may be more applicable and helpful in other scenarios to preserve the options and flexibility of QUERY when FILTER is not enough).

Like so: =UNIQUE(QUERY(A1:B6,"SELECT A WHERE B = 'SubCatA'"))

Answer 3

You need to add a helper column within the QUERY formula, like so:

=QUERY(
   {A1:B6, A1:A6},
   "SELECT Col1, COUNT(Col3) WHERE Col2 = 'SubCatA' GROUP BY Col1 LABEL COUNT(Col3) ''"
 )

Adding another QUERY formula will help to remove the helper column:

=QUERY(
   QUERY(
     {A1:B6, A1:A6},
     "SELECT Col1, COUNT(Col3) WHERE Col2 = 'SubCatA' GROUP BY Col1"
   ), 
   "SELECT Col1"
 )

Answer 4

Solution without helpers / wrapper functions:

=QUERY(A1:B6,"SELECT MAX(A) WHERE B = 'SubCatA' GROUP BY A LABEL MAX(A) ''")

Answer 5

Use the Unique(range) function.

Example:

=unique(query(Sheet2!A3:F2372,"SELECT C WHERE A contains '" &E1& "' AND B CONTAINS '" &E2& "'  ",-1))

This will work on one column retrieval. You can use this technique for further exploring your task. This is easier. A,B, C are columns from Sheet2 while E1, E2 are from current worksheet surrounded by &.

Answer 6

OP specifically asks via Google QUERY but there is precedent that another A does not require a QUERY and IMO a better solution (because more comprehensive, and versatile) would be to apply a pivot table:

This (and the other As) would be better demonstrations of solutions if the data sample was more realistic.

Answer 7

How about just:

= UNIQUE(A1:B6)