How to add column values in MySQL without using aggregate function?

MySQLMySQLi Database

You can add column values without using aggregate function like sum(). For that, the syntax is as follows −

SELECT *,(yourColumnName1+yourColumnName2+yourColumnName3,....N) as
anyVariableName from yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table AddingColumnDemo
   -> (
   -> StudentId int,
   -> StudentName varchar(20),
   -> MathMarks int,
   -> PhysicsMarks int,
   -> ChemistryMarks int
   -> );
Query OK, 0 rows affected (0.82 sec)

Insert records in the table using insert command. The query is as follows −

mysql> insert into AddingColumnDemo values(1,'John',35,45,76);
Query OK, 1 row affected (0.25 sec)

mysql> insert into AddingColumnDemo values(2,'Bob',67,76,88);
Query OK, 1 row affected (0.19 sec)

mysql> insert into AddingColumnDemo values(3,'Carol',45,56,43);
Query OK, 1 row affected (0.16 sec)

mysql> insert into AddingColumnDemo values(4,'Mike',82,75,71);
Query OK, 1 row affected (0.21 sec)

mysql> insert into AddingColumnDemo values(5,'Sam',92,89,88);
Query OK, 1 row affected (0.23 sec)

Display all records from the table using select statement −

mysql> select *from AddingColumnDemo;

The following is the output displaying the records of the table −

+-----------+-------------+-----------+--------------+----------------+
| StudentId | StudentName | MathMarks | PhysicsMarks | ChemistryMarks |
+-----------+-------------+-----------+--------------+----------------+
|         1 | John        |        35 |           45 |             76 |
|         2 | Bob         |        67 |           76 |             88 |
|         3 | Carol       |        45 |           56 |             43 |
|         4 | Mike        |        82 |           75 |             71 |
|         5 | Sam         |        92 |           89 |             88 |
+-----------+-------------+-----------+--------------+----------------+
5 rows in set (0.00 sec)

Let us now implement the query to add column values in MySQL −

mysql> select *,(MathMarks+PhysicsMarks+ChemistryMarks) as TotalResult from AddingColumnDemo;

The following is the output displaying the sum of column values in TotalResult column −

+-----------+-------------+-----------+--------------+----------------+-------------+
| StudentId | StudentName | MathMarks | PhysicsMarks | ChemistryMarks | TotalResult |
+-----------+-------------+-----------+--------------+----------------+-------------+
|         1 | John        |        35 |           45 |             76 |         156 |
|         2 | Bob         |        67 |           76 |             88 |         231 |
|         3 | Carol       |        45 |           56 |             43 |         144 |
|         4 | Mike        |        82 |           75 |             71 |         228 |
|         5 | Sam         |        92 |           89 |             88 |         269 |
+-----------+-------------+-----------+--------------+----------------+-------------+
5 rows in set (0.00 sec)
raja
Published on 31-Jan-2019 11:17:34
Advertisements