How to Multiply in SQL

Learning how to multiply in SQL is very easy and fast. SQL allows you to use basic mathematical operators in your queries. You can use fields within a table in your calculations as well as literal values.

strict female teacher with book pointing at scribbled blackboard

Basic SQL Multiplication

The most basic way to multiply in SQL is to use the asterisk (*), the mathematical operator for multiplication. Remember that the asterisk can also retrieve “all columns” from a table in a SELECT statement. But if SQL detects that you have a value on either side of an asterisk, it will treat it as a multiplication of the values on either side. Here is the most basic multiplication query.

SELECT 5 * 5;

SQL will return a single value of 25 when you run this query. You don’t even have to specify a table to select from in this case. But what if we want to multiply the value of two columns? A common scenario for this would be a sales transaction where multiple units are ordered and you want to calculate a total based on the unit price.

Let’s say we have a sales_records table that includes a quantity field and a unit_price field and we want to populate the transaction_amount field with the total. Our table might look like this to start with:

How to multiply in SQL - Figure 1 — Figure 1: Sales Records table

For our exercise, we want to set the transaction amount to be equal to the quantity times the unit price. To do that, we will use an update query and basic SQL multiplication.

UPDATE sales_records SET
    transaction_amount = quantity * unit_price;

Once we run this query, we’ll see that our values have been appropriately populated.

How to multiply in SQL - Figure 2 — Figure 2: Results of our update query

The great news here is that this functionality is very basic. It will work in PostgreSQL, MySQL, and SQL Server without any modification needed.

Also, take note of the data types at work here. The quantity field is a bigint and the unit_price and transaction_amount are of the money data type. So the multiplication result is in a money data type.

Multiplying a Field by a Literal Value in SQL

SQL allows you to multiply the values within a field by literal values as well. A literal value would be a specific number, for example, the number 5. Let’s assume that we wanted to calculate 9.5% sales tax on our transaction_amount field from our sales_records table. The query would look like this:

SELECT id, customer_name, transaction_amount, transaction_amount * 0.095 AS sales_tax
FROM sales_records;

This will give us the transaction_amount field and then a separate field that calculates 9.5% (0.095) of the transaction amount as the sales tax.

How to multiple in SQL - Figure 3 — Figure 3: Calculating the sales tax using a literal value in our query

Again, these operations are standard SQL and therefore they will work the same way in the various database platforms.

SQL Multiplication and Data Types

Multiplication and other mathematical operators will usually only work on numeric data types. This includes int, bigint, decimal, float, money and other numeric data types that may be specific to your database platform. What happens if we attempt to multiply a character field?

If we run the following query in PostgreSQL, you will see that we get an error message that indicates that we cannot perform this calculation. Similar messages will be returned in SQL Server and MySQL.

SELECT id, customer_name, transaction_amount,
    customer_name * 0.095
FROM sales_records;

Figure 4: Error shown in PostgreSQL when multiplying on a character field

Note that PostgreSQL will tell you: “You might need to add explicit type casts.” If you have a character-based field, such as a varchar field that contains a numeric value, you may be able to cast the field and perform the calculation.

This essentially means directing the database to treat the field as if it were a different data type, such as a bigint. However, if the database finds information that cannot be converted to a number then it will throw a different error. In general, you should avoid attempting this in a production setting because it can lead to errors and failures in your code.

The same will be true if you attempt to multiply against a date field. The following results are returned in PostgreSQL when attempting to multiply against our transaction_date field.

SELECT id, customer_name, transaction_amount,
    transaction_date * 0.095
FROM sales_records;

Figure 5: Error shown in PostgreSQL when multiplying on a datetime field

As you can see, multiplying in SQL is an easy task. You simply need to be aware of the data types that you’re working with to ensure that you don’t encounter serious issues in the process.

Want to learn SQL in depth? Try our Introduction to SQL Course for beginners!