Articles Cookbook
Back to list
Standard SQL

How to Multiply Two Columns in SQL

Database:

Operators:

Multiply two columns, JOIN, alias

Problem:

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.

idnamepricequantitydiscount_id
1pen731
2notebook582
3rubber1131
4pencil case2423

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:

nametotal_price
pen21
notebook40
rubber33
pencil case48

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.

idvalue
110
220
330

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:

nametotal_price
pen18.90
notebook32.00
rubber29.70
pencil case33.60

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.

Recommended courses:

go to top