Writing your first Sheets Query
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’”)
Note: A few videos from the course are cross-posted on Youtube, and have some discussion about the CIFL Youtube channel. I'm too lazy to edit the video twice, so hopefully you're not too bothered :).
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’”)
Note: A few videos from the course are cross-posted on Youtube, and have some discussion about the CIFL Youtube channel. I'm too lazy to edit the video twice, so hopefully you're not too bothered :).