How to Multiply Two Columns in SQL

Problem:

You want to multiply values from two columns of a table.

Example:

Our database has a table named purchase with data in the following columns: id, name, price, quantity, and discount_id.

Let’s multiply the price by the quantity of the products to find out how much you paid for each item in your order.

Solution:

SELECT name,
  price * quantity AS total_price
FROM purchase;

This query returns records with the name of the product and its total price:

Discussion:

Do you need to select the name of each record (in our case, name) and compute for it the result of multiplying one numeric column by another (price and quantity)? All you need to do is use the multiplication operator (*) between the two multiplicand columns (price * quantity) in a simple SELECT query. You can give this result an alias with the AS keyword; in our example, we gave the multiplication column an alias of total_price.

Note that you can also use data from two columns coming from different tables. We have another table in our database named discount that has columns named id and value; the latter represents the percent discount on the item with the given ID.

Look at the example below.

Solution:

SELECT p.name, 
 p.price * p.quantity * (100 - d.value)/100  AS total_price
FROM purchase p
JOIN discount d ON d.id = p.discount_id;

Here’s the result:

As you can see, it’s quite easy to multiply values from different joined tables. In our example above, we multiplied the price of each product by its quantity from one table (purchase) and then multiplied this total price by the percent discount using the discount table.