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
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
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!
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?