Search code examples
google-sheetsgoogle-query-language

add order by to query formula that gets unique list and count from delimited strings in a column Google sheets

I have a formula that gets a unique list of titles from pipe-delimited string in a column and there counts

=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN("|",Elements!$H2:$H),"|")&{"";""}),"select Col1, count(Col2) group by Col1 label count(Col2) ''",0))

I need to sort the counts in a descending manor, I have tried adding order by Col2 Desc

=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN("|",Elements!$H2:$H),"|")&{"";""}),"select Col1, count(Col2) group by Col1 label count(Col2) order by Col2 Desc''",0))

But I get unable to parse ...

Thank you

enter image description here

Solution

  • use:

    =INDEX(QUERY(TRANSPOSE(SPLIT(JOIN("|", Elements!H2:H), "|")&{"";""}),
 "select Col1,count(Col2) 
  group by Col1 
  order by count(Col2) desc
  label count(Col2)''", 0))