Using SQL queries in Google Sheets for Aggregating Values

Posted by Alan Barr on Mon 08 July 2019

Aggregating Values in Spreadsheets

I do not know if I am the only one that has looked at VLOOKUP or HLOOKUP in Excel and been mystified as to how to use these queries. A common programming task I have done is looping over one list of keys and matching to another list of key value pairs and then joining those datasets. This is pretty common in SQL databases and I can find examples all over the place. However, going through the work of putting a simple spreadsheet into a database for a simple summation seems like too much work to bother with.

For some reason my brain has not been able to internalize the spreadsheet commands for doing this type of analysis. Thankfully Google Sheets has a simple interface to do a simple SQL Query. I enjoy teaching software development in my spare time and I hope that it helps me keep my programming skills even though I do not work on day to day programming tasks. I have been tutoring online for about four years and I like to keep tabs on the data that is accumulating through the platform I teach.

The common data I need to review is, how much have I earned: overall, yearly, monthly; how is my teaching being rated yearly and overall. I could do this in a programming language imperative or functional but overall I still need to do quite a bit of hooking things together just to get results. Spreadsheets are the original reactive programming model but obviously missing the stringency of a real software development language.

Date Rating Earnings
6/7/2019 5 $56.25
6/6/2019 5 $27.50

I can plug into a cell

=QUERY(A:J,"select year(A), avg(G), sum(J) where (A is not null) group by year(A)",1)

and out pops

year(Date) avg Rating sum Earned avg Earned
2015 5 $$ +
2016 4.9 $$$ ++
2017 5 $$$ ++++
2018 5 $$$ ++
2019 4.87755102 $$$$ +++

From there I can select the values I want and create charts. Here I select my summed earnings and averaged earnings.

chart indicating rise and fall in avg lesson price but overall increase in earnings year over year

From there I can see trends fairly easily but I have to share the context as well. In 2017 I kept my rate pretty high and taught fewer lessons compared to other years. In 2019 I lowered my rates to compensate for a better fee structure from the platform I receive leads from. My average rate is not as high as its ever been but its lead to more consistent business.

I think this is a general trend in technologies and businesses. As time passes we should be seeing the costs drop for services as clients receive more value for their money and they'll more likely want to continue to participate otherwise they'll find a better alternative.