# 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.

## 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:

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.

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.

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;
```

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;
```

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!