In this guide, we will dive back into the basics and learn how we can perform the multiplication or table columns in SQL. If you are just getting started in SQL, this will offer a good foundation or other kind of column operations that are supported in your database.
Sample Table
Before diving in, let us setup a basic table that will help us to demonstrate the multiplication operations and results.
For this one, we set up a “products” table and insert the sample data as shown in the following example:
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255),
category VARCHAR(255),
price DECIMAL(10, 2),
quantity INT,
expiration_date DATE,
barcode BIGINT
);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Chef Hat 25cm',
'bakery',
24.67,
57,
'2023-09-09',
2854509564204);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Quail Eggs - Canned',
'pantry',
17.99,
67,
'2023-09-29',
1708039594250);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Coffee - Egg Nog Capuccino',
'bakery',
92.53,
10,
'2023-09-22',
8704051853058);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Pear - Prickly',
'bakery',
65.29,
48,
'2023-08-23',
5174927442238);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Pasta - Angel Hair',
'pantry',
48.38,
59,
'2023-08-05',
8008123704782);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Wine - Prosecco Valdobiaddene',
'produce',
44.18,
3,
'2023-03-13',
6470981735653);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Pastry - French Mini Assorted',
'pantry',
36.73,
52,
'2023-05-29',
5963886298051);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Orange - Canned, Mandarin',
'produce',
65.0,
1,
'2023-04-20',
6131761721332);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Pork - Shoulder',
'produce',
55.55,
73,
'2023-05-01',
9343592107125);
insert
into
products (product_name,
category,
price,
quantity,
expiration_date,
barcode)
values ('Dc Hikiage Hira Huba',
'produce',
56.29,
53,
'2023-04-14',
3354910667072);
Once we have the sample data setup, we can proceed and learn how to multiply.
SQL Multiply Syntax
In SQL, we perform the arithmetic multiplication using the “*” operator. The following is an example syntax:
FROM TABLE_NAME;
Here, “column1” and “column2” are the numeric columns that we wish to multiply. The “result_column” refers to an alias for the column that contains the result of the multiplication.
Examples:
Let us explore some examples on how to perform the multiplication on the target columns.
Example 1: Multiply from the Same Table
The most basic multiplication tasks involve columns of the same table. For example, suppose we want to calculate the total value of each product as shown in the following example:
product_name,
price,
quantity,
(price * quantity) AS total_rev
FROM
products;
In this example, we use the “*” operator to multiple the unit price and quantity of each row and assign an alias to the resulting column as “total_rev”.
Example Output:
Example 2: Multiply from Different Tables
There’s also instances where we need to incorporate the data from another table. Let us take an example where we have another table called “discounts” and we want to calculate the discounted price for each product.
FROM products p
JOIN discounts d ON p.product_id = d.product_id;
In this example, we join the “products” and “discounts” tables on the “product_id”. We then use the multiply operator on the resulting columns from the join.
Conclusion
In this tutorial, we learned the fundamentals of SQL by learning how to use and work with the multiply operator.
