How to Add in SQL

A common question for people learning to write SQL code for the first time is how to add in SQL. Fortunately, it’s a very simple process, but there are some things to be aware of when performing mathematical operations in SQL. Let’s take a look at the process.

Basic Addition in SQL

The simplest way to add in SQL is to simply use the addition operator (+) to add two numbers together. That query would look like this:

SELECT 1 + 1;

As you can imagine, this query will return a single value of 2. This query will work on virtually any database engine you use – MySQL, PostgreSQL, and SQL Server.

When we learned how to multiply in SQL, we found that we could perform not only math operations on literal values but also on column values as well. The same is true with addition operations. We can add values from two columns within a table simply using the plus operator.

Let’s say that in our sales_records table we decide to add a field to keep track of the sales tax. We’ll call this field sales_tax and it will be a data type of money in MS SQL and PostgreSQL and a decimal(12, 4) in MySQL. We can calculate the sales tax as a fixed percentage as we did in the multiplication article referenced above. For this example, we’ll use 7.5% as the sales tax amount. For now, we’ll have our transaction amount as a NULL value in the SQL table. Once I’ve updated my table, the results will look like this.

If you’re interested in the update query, it looks like this:

UPDATE sales_records SET
    sales_tax = (quantity * unit_price) * 0.075,
    transaction_amount = null;

What we want to do now is to add our sales tax amount to the total amount for the number of units purchased to see what the order total will cost. We can do that with a query that multiplies the quantity and unit price and then adds the sales tax amount. Keeping in mind our order of operations, we’ll use parentheses around our multiplication operation to ensure correct results. It also makes our query easier to read.

SELECT id, transaction_date, customer_name, quantity, unit_price, sales_tax,
    (quantity * unit_price) + sales_tax AS transaction_amount_calculation
FROM sales_records;

Mixing Literal Values and Columns

Now that we have the total amount for the transaction we could update our transaction_amount field to store this. But let’s say that we charge a flat amount of $5.99 for shipping and handling. What if we want to add that amount to the transaction as well? (In the real world, we would probably get this dynamically from an API but for our example, we’ll use a fixed amount.)

We can simply add $5.99 to our transaction amount calculation. The query would look like this:

SELECT id, transaction_date, customer_name, quantity, unit_price, sales_tax,
    (quantity * unit_price) + sales_tax + 5.99 AS transaction_amount_calculation
FROM sales_records;

This will return the same value as before except it will add 5.99 to the transaction_amount_calculation. This query works exactly the same way for MySQL and MS SQL. PostgreSQL will give us an error that tells us: “Operator does not exist: money + numeric”. PostgreSQL is not inferring the data types the same way the other database engines do. So for PostgreSQL, we have to explicitly “cast” the value. This means we tell PostgreSQL to treat “5.99” as a money value.

SELECT id, transaction_date, customer_name, quantity, unit_price, sales_tax,
    (quantity * unit_price) + sales_tax + 5.99::money AS transaction_amount_calculation
FROM sales_records;

We can now store our total amount in the transaction_amount field with a simple update query. Don’t forget to adjust the query to explicitly cast the shipping amount in PostgreSQL.

UPDATE sales_records SET
    transaction_amount = (quantity * unit_price) + sales_tax + 5.99;

SQL Addition and Data Types

As with other mathematical operators in SQL, we have to be concerned with data types as well. If we try to add values to our customer_name or transaction_date fields, we are going to get similar type mismatch errors as we did when attempting multiplication. Refer to the article linked above for details.

Also, keep in mind that if a value in a column is NULL, then a math operator applied to that value will always return NULL. If we run the following query, you can see an example of how this will work.

SELECT 1 + NULL;

This is because NULL is the absence of any value. Therefore, if we perform an operation on it, the NULL value takes presence. The same is true when we use aggregate functions in SQL, such as the SUM() function.