How to calculate value from multiple columns in MySQL?

MySQLMySQLi Database

To calculate a value from multiple columns, use GROUP BY. Following is the syntax −

select yourColumnName1,sum(yourColumnName2*yourColumnName3) AS anyAliasName
from yourTableName group by yourColumnName1;

Let us first create a table −

mysql> create table calculateValueDemo
   -> (
   -> Id int,
   -> ProductPrice int,
   -> ProductWeight int
   -> );
Query OK, 0 rows affected (0.56 sec)

Following is the query to insert records in the table using insert command −

mysql> insert into calculateValueDemo values(100,35,5);
Query OK, 1 row affected (0.16 sec)

mysql> insert into calculateValueDemo values(101,50,3);
Query OK, 1 row affected (0.16 sec)

mysql> insert into calculateValueDemo values(100,100,4);
Query OK, 1 row affected (0.17 sec)

mysql> insert into calculateValueDemo values(101,500,2);
Query OK, 1 row affected (0.22 sec)

Following is the query to display all records from the table using select statement −

mysql> select * from calculateValueDemo;

This will produce the following output −

+------+--------------+---------------+
| Id   | ProductPrice | ProductWeight |
+------+--------------+---------------+
| 100  | 35           | 5             |
| 101  | 50           | 3             |
| 100  | 100          | 4             |
| 101  | 500          | 2             |
+------+--------------+---------------+
4 rows in set (0.00 sec)

Here is the query to calculate the value from multiple columns −

mysql> select Id,sum(ProductPrice*ProductWeight) AS Total from calculateValueDemo group
by Id;

This will produce the following output −

+------+-------+
| Id   | Total |
+------+-------+
| 100  | 575   |
| 101  | 1150  |
+------+-------+
2 rows in set (0.00 sec)
raja
Published on 16-Apr-2019 17:06:27
Advertisements