SQL for accountants: a crash course
A hands-on introduction for accountants
SQL is the number one computer programming to learn if you're dealing with data. Find out how you can start using it today with our crash course.
Accountants and FinOps Experts have something in common: we love data. We analyse a ton of data so we can provide greater business insights.
With this being a huge part of our job and SQL being the language of data and databases, it's the computing language to learn!
While there are many courses that teach SQL, none focus on the accounting side. We'll let you know what tools you'll need, run through some key terms and then get you started on a real project! This quick course has been designed with one of the fundamental philosophies of the FinOps Academy: learning through experience.
To get the most out of this course, you'll want to follow along yourself. So to start, if you haven't already got one, we recommend signing up for a free Google account as Google Sheets is the perfect tool for learning SQL .
I know I know, you probably spend the majority of your day using Excel or Google sheets. The last thing you want to do is spend more time using them! However, using the Query function within Google Sheets enables you to use Google Visualisation API Query language (built on SQL) to directly write SQL based code!
It’s a great way to teach theory and application without needing to install any complex programmes!
Below are the language clauses you will need to use. When you use them, you use them in the order below. They do not need to be separated by commas.
select: selects which columns to return, and in what order. If omitted, all of the table's columns are returned, in their default order.
💡Remember to separate identifiers with a comma (,)
where: returns only rows that match a condition. If omitted, all rows are returned.
💡Remember to add ' ' when using for text. The simple comparison operators are <=, <, >, >=, =, !=, <>."
group by: aggregates values across rows.
order by: sorts rows by values in columns.
💡 Use Asc for ascending and Desc for descending. E.g. order by K asc
limit: limits the number of returned rows.
label: sets column labels.
💡Having this empty will remove all column labels. E.g. label count(A)". You must include the aggregation clause with the identifier (e.g. count(B))
These can be used on the identifier (e.g. A) to return a value.
Aggregation functions can be used in select, order by, label, format clauses. They cannot appear in where, group by, pivot, limit, offset, or options clauses.
➡️ avg(): returns the average value of all values in the column for a group.
➡️ count(): returns the count of elements in the specified column for a group. Null cells are not counted.
➡️ max(): returns the maximum value in the column for a group. Dates are compared with earlier being smaller, strings are compared alphabetically, with case-sensitivity.
➡️ min(): returns the minimum value in the column for a group. Dates are compared with earlier being smaller, strings are compared alphabetically, with case-sensitivity
➡️ sum(): returns the sum of all values in the column for a group.
💡 If you want a count of a column, first select it! For example, Select Count(B).
You can find the above introduction to SQL for accountants crash course copied to this Google Sheet. You will need to make a copy and save your own file to your drive. It will have the intro to the basics of the language then gets you started on a big project to test your skills.
This course is designed for you to do in your own time. Simply copy the sheet and work through the tasks as and when you like.
If you liked this crash course and are looking for more hands-on practice with computer programming languages and accounting software, you should check out the FinOps Academy.
Born from the frustration of accounting training not setting us up for the real-world, we launched the FinOps Academy. We've got fantastic courses that teach you innovative ways to automate and let you try out impressive software we actually use at Quantico.
Split into 2 types, you can either read through our need-to-know fundamentals or get stuck in immediately with our real-world FinOps problems. Learn anything from how to build a financial model using Causal to how to bring payroll in-house with Payfit. We've also got more courses in the works so be sure to keep checking the website and our socials!