0

I'm getting a '#Value!' error when I try to sort data returned by a QUERY statement. This is the basic statement:
=query('Raw Data'!A:G,"Select A, C, D, sum(G) where B='instagram' and (D='Female Followers Age' or D='Male Followers Age') and A <> '#N/A' group by D, C, A label A 'Handle', C 'Country', D 'Segment', sum(G) 'Share'",0)

Adding an ORDER BY statement to the end of it to sort on column A generates a #Value! error. I saw some responses on forums suggesting column numbers should be used in place of letters with the order by clause. However, I have tried replacing order by A with order by 1, but it generates the same error.

=query('Raw Data'!A:G,"Select A, C, D, sum(G) where B='instagram' and (D='Female Followers Age' or D='Male Followers Age') and A <> '#N/A' group by D, C, A label A 'Handle', C 'Country', D 'Segment', sum(G) 'Share' order by A",0)

1 Answer 1

0

In a query statement, the clauses must be given in the order specified in the query language help page. The order by clause must come after group by and before label, like this:

=query( 
  'Raw Data'!A:G, 
  "select A, C, D, sum(G) 
   where B = 'instagram' 
   and (D = 'Female Followers Age' or D = 'Male Followers Age') 
   and A <> '#N/A' 
   group by D, C, A 
   order by A 
   label A 'Handle', C 'Country', D 'Segment', sum(G) 'Share' ", 
  0 
)
4
  • That is great. Thank you!
    – S Jacobs
    Sep 17, 2021 at 9:22
  • On a related note does including the pivot clause affect the use of order by? In the statement below including the order by clause returns a #VALUE error even though I have placed it before the label clause. If I use col E to sort the data then the results are returned correctly. Since the pivot clause turns 'sum(F)' into 2 columns I'm guessing I need to specify which of the two columns to sort on - but am not sure how to. =query('Raw Data'!A:G,"Select E, sum(F) where A='"&Size!G1&"' and D='Brand Followers' and G>0.05 group by E pivot C order by sum(F) desc label E 'Brand'",0)
    – S Jacobs
    Sep 17, 2021 at 9:41
  • I gather that this is your first question at Stack Exchange. See What should I do when someone answers my question? Sep 17, 2021 at 9:47
  • Please ask only one question per post. Sep 17, 2021 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.