Remedied by expanding your data range to include the column (tweets!A1:D in this case). =query(tweets!A1:C,‘select C, sum(D) where D >= 0 group by C limit 2’,0) When you’re selecting a column that’s outside of your range: =query(tweets!A1:D,‘select C, sum(D) where D >= 0 group by C limit 2 label sum© “,0) =query(tweets!A1:D,‘select C, sum(D), where D >= 0 group by C limit 2’,0)Ĭaused by a mismatch between columns in your ‘select’ clause and your ‘label’ clause: Would mean there’s a syntax error somewhere surround my ‘where’ clause – a hanging comma or a typo: The sheet that you’re referencing in your query doesn’t exist – probably due to a typo or having deleted it :(.Ĭaused by a misinformed query string – it will always tell you exactly where to look: There’s data in cell D6 blocking your query from expanding – remove it! #REF 1) Array result was not expanded because it would overwrite data in D6 But if you do ever forget the syntax, the CIFL cheat sheet has your back.Įrrors are never fun, but Sheets’ error messages will always point you in the right direction. Once you get the hang of it, you’ll never think about it again. Use the TEXT function to convert the date into the correct ‘yyyy-mm-dd’ format.To combine the string with an outside value, close the query double-quotes, and use ‘&’.I like to keep date ranges (last 7 days, last 30 days, year-to-date, etc), then reference them in a query like this: If you want to compare a date dynamically from another cell, it’s a bit of a different story. Type your date in the format ‘yyyy-mm-dd’.Write the string ‘date’ before the actual date, to declare your intentions.Unfortunately it’s not really mentioned anywhere in the Google Sheets documentation, so I had to figure it out for myself. When I first started writing date comparisons within Google queries (pulling data before / after a certain date), it was a pain to figure out the correct syntax. Why so complex in Sheets queries – shouldn’t this be easier? Q: How many retweets did each user in the list receive?Ī: =query(D1:G, “select F, sum(G) where E != ‘’ group by F label sum(G) ‘total retweets’”) Q: What was the average number of retweets on Tweets that contained an but weren’t a retweet?Ī: =query(D1:G, “select avg(G) where E contains and not E contains ‘RT’”) Q: What was the average number of retweets from the sample? =query( ‘data tab’!A:C, “select A where C = ‘twitter’”)Įxample QUERY functions to answer questions And a query string – “select A where C = ‘twitter’”.The basic structure of a QUERY is to input: In this example, we’ll walk through a few QUERY examples, to analyze a sampling of Twitter data. QUERY combines all of the capabilities of arithmetic functions (SUM, COUNT, AVERAGE) with the filtering abilities of a function like FILTER. Btw…if you’re looking to make the jump from Sheets to BigQuery, check out our BigQuery quickstart Recipes over at query.recipes.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |