Order counted and filtered Google Sheets Query Results

April 19, 2022

I am working on a Google Sheet intended to aid in the analysis of Marketing-related KPIs. For this, I have created (or rather researched from different sources) my first Query, which looks up different columns from a different sheet based on one column "date" and groups them while counting how many times different values appear.
Now, I would like to also sort this query by the counted values.

The code currently looks like this:

=QUERY(UNIQUE(QUERY(
Gruppenname_klaeren!A3:I;"SELECT * WHERE I is not null 
and B >= date'"&TEXT(today()-A2;"yyyy-mm-dd")&"'
and B <= date '"&TEXT(today();"yyyy-mm-dd")&"'
label I 'Kampagne'")); 
"SELECT Col9, count(Col3) WHERE Col9 is not null GROUP BY Col9 label count(Col3) 'Anzahl GKs'")

Honestly, the code feels like a mess – but that’s the only way I could make it work. Now, I would like to order the results based on the values of count(Col3), but just inserting this in the following way doesn’t work:

"SELECT Col9, count(Col3) WHERE Col9 is not null GROUP BY Col9 label count(Col3) 'Anzahl GKs' order by count(Col3) desc")

If anyone knows why this doesn’t work, I’d really appreciate the help.

Thanks!

>Solution :

Try

=SORT(QUERY(UNIQUE(QUERY(
Gruppenname_klaeren!A3:I;"SELECT * WHERE I is not null 
and B >= date'"&TEXT(today()-A2;"yyyy-mm-dd")&"'
and B <= date '"&TEXT(today();"yyyy-mm-dd")&"'
label I 'Kampagne'")); 
"SELECT Col9, count(Col3) WHERE Col9 is not null GROUP BY Col9 label count(Col3) 'Anzahl GKs'");3;0)

I have encapsulated your formula by sort(___________;3;0)

1 = ASC
0 = DESC