How to Spend Less by Mastering the Monthly Budget in SQL

Do you struggle to keep your budget under control? You must have heard about various budget planning materials available as printouts, Excel spreadsheets, or smartphone apps. In this article, we’ll show you another possibility. You’ll learn how to use databases and SQL to identify the weak points of your home budget and spend less.

We all need to pay for everyday necessities such as groceries or medical appointments. With money coming in and out of our pockets, it’s easy to lose track of how much we spend and if we have any savings.

That’s why it’s important to verify if we spend as much (or as little) as we want to by looking at our past expenses. Based on that analysis, we can plan a budget for future months.

There are various ways to analyze your budget. You can take a good old piece of paper and write down all of your income and expenses. You can make use of some ready-made Excel spreadsheets available online. You can also download an app for your smartphone.

In this article, we’ll show you another possibility. We’ll use a simple database table to demonstrate how you can analyze your budget with SQL. Don’t worry if you have no experience in this area—this article will explain everything step-by-step. We’ll also tell you how to efficiently learn SQL if you’re interested in acquiring new skills.

This article doesn’t require any knowledge of SQL, but we expect that you understand the general idea behind relational databases. If you’ve never heard about databases and SQL, have a look at two short YouTube videos that we’ve prepared on our channel. Both of them are around four minutes long and will give you a short introduction to the world of relational databases.

The sample income and expenses discussed in this article are for a one-person household. You can also apply the same rules for a small company. We’ve prepared some sample data for the first quarter of 2020 (January, February, and March) to show you how you can create and analyze a budget.

Input Data

First, let’s talk about the data structure. In our database, we’ll create a single table named budget_item. This table will hold information about all our expenses and income. There will only be four columns:

• Date—The date of the expense or income.
• Category—The item category, such as “food” or “health” (a maximum of 32 characters).
• Description—The detailed description of the given item, such as “rent” or “electricity bill” (a maximum of 128 characters).
• Amount—The amount that we earned (a positive number, e.g., 349) or spent (a negative number, e.g., -17).

For this article, we’ll assume that the budget_item table is already filled with all the expense and income sources. You can download a .sql file that we’ve prepared and run it on your local database. It will create the table for you and fill it with the rows we discuss in this article. The file is available here.

The SQL script will work with most popular relational databases such as SQL Server or PostgreSQL and will create the table for you. The file above will create the table discussed in this article and insert the sample data we’ve prepared for demonstration purposes.

If you’d like to learn to create SQL tables yourself, you can take a look at our Creating Database Structure track. Here you will find information on how to create tables, what the data types are, and what SQL Views and SQL Indexes are.

If you want to analyze the datasets with your own income and expense items, you will have to find a way of importing them into your database. Typically, you will need CSV files. Most relational databases let you import CSV files with your data directly. Some of them will even create the tables for you automatically.

With a bit of playing around, you should be able to download your smartphone budget app data as CSV files. Many bank websites also let you download the information about your credit card transactions as CSV files.

Excel is another idea—it lets you convert spreadsheets into CSV files. So, if you’ve used it as your budget planning tool so far, you won’t have a problem importing the data into a database. Have a look around the internet—there are plenty of articles about how you can do all of that.

Ready, Steady, Go!

Now that we have all the data in place, let’s see how we can use SQL to analyze the budget and identify possible places for improvement.

First of all, we’ll write a very simple SQL query that will list all the information in our table. This will be a quick check of whether the data we need is in place.

SELECT * FROM budget_item;

The asterisk symbol (*) in SQL means “everything.” When we run the query, we should see something similar to the picture below. The screenshot comes from the PostgreSQL admin panel, so your query result may look a bit different if you use another database engine.

Perfect. Now that we know the data is there, let’s answer the most important questions: What’s the quarterly balance? Do we have any money left? With our table, the answers are easy to find:

SELECT SUM(amount) FROM budget_item;

We used the SUM() function with the amount column inside to get the overall sum of all items (both the expenses expressed as negative numbers and the income expressed as positive numbers) in our table. The result is as follows:

Oops! As you can see, we exceeded the quarterly budget by $893. Not good! Let’s try to analyze how that happened and if there’s any room for improvement. First, we’ll want to find the balance for each month individually. To that end, we’ll use the following query.

SELECT EXTRACT(MONTH FROM date), SUM(amount)
FROM budget_item
GROUP BY EXTRACT(MONTH FROM date)
ORDER BY EXTRACT(MONTH FROM date);

The query we wrote now is a bit more complicated. We used a time function named EXTRACT to extract the month from each date. We also used this function to group all rows by the month. Finally, we sorted the rows by the month. The month value will be a number from 1 (January) to 3 (March).

EXTRACT() and other time-related functions are covered in our SQL course entitled Standard SQL Functions. Check it out!

When we run the query, we get the following result.

This doesn’t look good either. Not only do we have a negative balance each time, but we also have a bigger debt each month. Let’s try to analyze what exactly made us exceed the budget so much. This time, we’ll write an instruction that will show the monthly sums for individual categories. We’ll use the following query:

SELECT EXTRACT(MONTH FROM date), category, SUM(amount)
FROM budget_item
GROUP BY EXTRACT(MONTH FROM date), category
ORDER BY EXTRACT(MONTH FROM date), SUM(amount);

We’ve added an ORDER BY clause at the end. Now, all the categories for each month will be shown together, and the category where we spent most will be shown first. This is what we’ll see when we run the query.

Let’s try to analyze the resulting table. The “house” expenses are similar each month, and there‘s not much we can do about them anyway. But take a look at the “food” category—why do we spend more each month? We can see a similar problem with the “entertainment” and “transport” categories. Let’s analyze them one-by-one in detail. We’ll start with the “food” category.

SELECT EXTRACT(MONTH FROM date), description, amount
FROM budget_item
WHERE category = 'food'
ORDER BY EXTRACT(MONTH FROM date);

We’ve added a WHERE clause to only show items from the “food” category. The result should look like this.

Even though we spend less and less money on eating out each month, we spend much more on groceries. Chances are that we thought we could add more to our supermarket cart because we ate out less, but we ended up paying more for the food.

Excellent, we’ve just identified the first possible improvement: we should pay attention to what we put into our cart. Now, let’s run a similar query for the “entertainment” category.

SELECT EXTRACT(MONTH FROM date), description, amount
FROM budget_item
WHERE category = 'entertainment'
ORDER BY EXTRACT(MONTH FROM date);

This query is very similar to the previous one. We’ll get the following result.

We seem to choose more expensive entertainment options each month. The PC game we bought in March is nearly twice as expensive as the one we bought in February. The museum we attended in March cost much more than that in January. And the theatre play from March is four times as expensive as the one we saw in January.

So, another conclusion is that we should pay attention to the entertainment options, and maybe look for those that are less expensive. Finally, let’s look at the last category: transport. Again, we’ll use a similar query.

SELECT EXTRACT(MONTH FROM date), description, amount
FROM budget_item
WHERE category = 'transport'
ORDER BY EXTRACT(MONTH FROM date);

We should see the following result.

Here, the conclusion is quite obvious: we spent much more on fuel in March than we spent on metro tickets in the first two months. We could save some money by using public transport instead of a car.

Conclusions From the Budget Analysis

By running the queries above, we found three categories of possible improvement in our budget: food, entertainment, and transportation. We now have an idea of what we can do to spend less.

As you can see above, we didn’t use any sophisticated analytical methods to find our weak spots. We used a single database table and short SQL commands to quickly identify how we can improve our quarterly budget. Whether you want to manage your home or company budget—you can use SQL to do it effectively and efficiently.

How to Learn SQL

At LearnSQL.com, we teach SQL from scratch in a completely interactive manner. If you don’t know where to start, take a look at our SQL Fundamentals Mini Track. Here, we offer a smooth entry into the world of coding.

You don’t need any prior experience with SQL or any other programming language. You don’t need to worry about the technical setup either—all the learning takes place in your web browser. We take care of the database while you focus on learning the key concepts of SQL.

If you want to know more about the benefits of learning with us before you purchase a subscription, take a look at our article Why Take the SQL Basics Course at LearnSQL.com. Agnieszka, our Chief Content Officer, explains in detail why we built the course, what it contains, and the philosophy behind it.

The SQL courses mentioned above are meant for beginners, but we also have courses for more advanced SQL users. If you want to perform sophisticated trend analyses in SQL, we’ve got you covered. Take a look at Revenue Trend Analysis in SQL.

If you liked this article, you may want to also read SQL Hacks To Control Family Budget On Black Friday Weekend.