Google Sheets – Return DISTINCT/UNIQUE List of Text via QUERY

google sheetsgoogle-sheets-query

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?

Best Answer

Without a query:

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

Related Solutions

Google Sheets – Google Spreadsheet Query Blanks Out Text Cells in the Query Range

QUERY result assigns a data type to each column. The "blanked out cells" occurs because their data type is not compatible with the data type of the resulting column.

Instead of QUERY use FILTER.

To learn more about QUERY see https://support.google.com/docs/answer/3093343?hl=en and https://developers.google.com/chart/interactive/docs/querylanguage.

Google Sheets – Using IMPORTXML Function to Download Multiple Columns

It's not possible to achieve the desired result by using IMPORTXML alone, I think. Instead use Google Apps Script and the XML Service Service.

Example:

Adaptation of the code taken from the answer by Vidar S. Ramdal

function parseFeedXml(url) {
   var result = [];
   var xml = UrlFetchApp.fetch(url).getContentText();
   var document = XmlService.parse(xml);
   var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
   var feed = document.getRootElement();
   var entry = feed.getChildren('entry',atom);
   for (var i = 0; i < entry.length; i++) { 
     var content = entry[i].getChildren('content',atom);
     var contentElement = '';
     var resultRow = [];
     for (var j = 0; j < content.length; j++) { 
       contentElement = content[j];
       evaluateText(contentElement.getChild('filing-date',atom), resultRow);
       evaluateText(contentElement.getChild('filing-type',atom), resultRow);
       evaluateText(contentElement.getChild('filing-href',atom), resultRow);
       evaluateText(contentElement.getChild('form-name',atom), resultRow);
       evaluateText(contentElement.getChild('size',atom), resultRow);
       evaluateText(contentElement.getChild('amend',atom), resultRow);
       result.push(resultRow);                
     }
   }
   return result;
}

function evaluateText(element, resultRow) {
  if (element) {
    resultRow.push(element.getText());
  } else {
    resultRow.push('');
  }
}

Best Answer

Related Solutions

Google Sheets – Google Spreadsheet Query Blanks Out Text Cells in the Query Range

Google Sheets – Using IMPORTXML Function to Download Multiple Columns

Related Topic