SQL for Accountants

My objective here is not to teach you how to write SQL queries.  Instead I am going to draw some comparisons between every accountants favorite tool, Excel, and SQL commands.  My hope is that the analogies I make will help improve your understanding of SQL.

So to get started lets look at an Excel workbook as the database and the individual worksheets as files within the database.  For this exercise we will limit worksheets to information sets only or tables.  Think of the objective of SQL in terms of pulling relevant and related information into a new worksheet.

The first command I would like to cover is the From command.  In Excel this would be equivalent to referencing a worksheet in a formula.  It directs your formula to which worksheet you would like to pull information.

The next command is the Select statement.  This is going to reference the header information contained in each the worksheet.   Much like using an index function.

The where command is like using a filter.  You will specifically designate the information in the Select statements fields that you would like to retrieve.  This is your criteria.

Next is the Join statement this is where things get a little complicated.  I like to think of this statement as though it is a vlookup.  The only difference is that this information doesn't have to be to the far left or do you have to reference columns.  (If you are an advanced Excel use Index(range,match(field)) may be a better explanation).  Here what is important is that information matches from both tables and you can reference the information against each other.   Joins define the relationship between the tables (worksheets to use our Excel terminology).  Note that you would join using the field names.

Let's cover the OrderBy command next to take a break.  This is the same as using Sort.

Now let's cover the GroupBy command.  This is like using PivotTables.  The main thing to remember here is that this is going to summarize the data by the fields you chose to group.  Now the trick is that every field in the Select statement needs to be included in the group by statement unless it is a calculation or aggregate of the other data.

For now I am going to leave this post as is, I may add and revise some information later.  My head hurts.