Google Sheets QUERY – Using the HAVING Clause in Google Sheets QUERY

formulasgoogle sheetsgoogle-sheets-query

My Problem

I have built a count query that returns the most common elements from column H in the final sheet:

 =QUERY(final!H2:H,"SELECT H, COUNT(H) GROUP BY H ORDER BY COUNT(H) DESC")

I would like to filter out elements that appear only once. In traditional SQL I would add:

 HAVING COUNT(H)>1

However, this fails with #VALUE! on Google Sheets.

My Question

How can I filter out query elements based on their count?

Best Answer

Based on this Google Forum answer, I've nested the query:

=QUERY(
      QUERY(
          final!H2:H,
          "SELECT H, COUNT(H) GROUP BY H ORDER BY COUNT(H) DESC"
      ), 
      "WHERE Col2 > 1")

Note that the column reference in the outer query is case sensitive, and must be exactly Col2.

Related Topic