The Google Sheets Query: Forget your other Functions
Excerpted from the advanced Google Sheets course, Data Analysis the Lazy Way.
QUERY combines all of the capabilities of arithmetic functions (SUM, COUNT, AVERAGE) with the filtering abilities of a function like FILTER.
In this example, we’ll walk through a few QUERY examples, to analyze a sampling of Twitter data.
The basic structure of a QUERY is to take a data range and a query string:
=QUERY( ‘data tab’!A:C, “select A where C = ‘twitter’”)
Let’s answer a few questions, all using QUERY functions:
Q: What was the average number of retweets from the sample?
A: =query(D1:G, “select avg(G)”)
Q: What was the average number of retweets on Tweets that contained an @mention but weren’t a retweet?
A: =query(D1:G, “select avg(G) where E contains ‘@’ and not E contains ‘RT’”)
Q: How many retweets did each user in the list receive?
A: =query(D1:G, “select F, sum(G) where E != ‘’ group by F label sum(G) ‘total retweets’”)
For more background on all of the QUERY syntax covered in this video, grab a copy of my Google Sheets formula cheat sheet at https://trello.com/c/b2q3P0E3/39-10-k...