Select and add result of multiplying two columns from a table in MySQL?

You can use aggregate function SUM() for this. Let us first create a table −

mysql> create table DemoTable    (    CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    CustomerProductName varchar(100),    CustomerProductQuantity int,    CustomerPrice int    ); Query OK, 0 rows affected (0.17 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(CustomerProductName,CustomerProductQuantity,CustomerPrice) values('Product-1',5,400); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(CustomerProductName,CustomerProductQuantity,CustomerPrice) values('Product-2',3,100); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable(CustomerProductName,CustomerProductQuantity,CustomerPrice) values('Product-1',2,300); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable(CustomerProductName,CustomerProductQuantity,CustomerPrice) values('Product-1',5,50); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable(CustomerProductName,CustomerProductQuantity,CustomerPrice) values('Product-3',6,10); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable(CustomerProductName,CustomerProductQuantity,CustomerPrice) values('Product-2',10,20); Query OK, 1 row affected (0.03 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+------------+---------------------+-------------------------+---------------+ | CustomerId | CustomerProductName | CustomerProductQuantity | CustomerPrice | +------------+---------------------+-------------------------+---------------+ | 1          | Product-1           | 5                       | 400         | | 2          | Product-2           | 3                       | 100 | | 3          | Product-1           | 2                       | 300 | | 4          | Product-1           | 5                       | 50 | | 5          | Product-3           | 6                       | 10 | | 6          | Product-2           | 10                      | 20 | +------------+---------------------+-------------------------+---------------+ 6 rows in set (0.00 sec)

Following is the query to select and add result of multiplying two columns (CustomerProductQuantity*CustomerPrice) from a table in MySQL.

mysql> select CustomerProductName, SUM(CustomerProductQuantity*CustomerPrice) AS TOTAL_PRICE from DemoTable group by CustomerProductName;

This will produce the following output −

+---------------------+-------------+ | CustomerProductName | TOTAL_PRICE | +---------------------+-------------+ | Product-1 | 2850 | | Product-2 | 500 | | Product-3 | 60 | +---------------------+-------------+ 3 rows in set (0.00 sec)