How can I add a new column which counts the number of rows as serial number in MySQL?

MySQLMySQLi Database

To add a new column that counts the number of rows as serial number, you can use the global variable in select statement.

Let us create a table. The query to create a table is as follows:

mysql> create table addColumnToCountAsSerialNumber
   -> (
   -> Id int,
   -> Name varchar(20),
   -> Age int,
   -> Salary int
   -> );
Query OK, 0 rows affected (0.80 sec)

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

mysql> insert into addColumnToCountAsSerialNumber values(10,'John',23,8576);
Query OK, 1 row affected (0.10 sec)
mysql> insert into addColumnToCountAsSerialNumber values(12,'Carol',21,4686);
Query OK, 1 row affected (0.14 sec)
mysql> insert into addColumnToCountAsSerialNumber values(9,'Mike',22,38585);
Query OK, 1 row affected (0.11 sec)
mysql> insert into addColumnToCountAsSerialNumber values(15,'Sam',25,38586);
Query OK, 1 row affected (0.16 sec)
mysql> insert into addColumnToCountAsSerialNumber values(20,'Bob',26,43544);
Query OK, 1 row affected (0.17 sec)
mysql> insert into addColumnToCountAsSerialNumber values(39,'Larry',29,485886);
Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from addColumnToCountAsSerialNumber ;

The following is the output:

+------+-------+------+--------+
| Id   | Name  | Age  | Salary |
+------+-------+------+--------+
|   10 | John  |  23 |    8576 |
|   12 | Carol |  21 |    4686 |
|    9 | Mike  |  22 |   38585 |
|   15 | Sam   |  25 |   38586 |
|   20 | Bob   |  26 |   43544 |
|   39 | Larry |  29 |  485886 |
+------+-------+------+--------+
6 rows in set (0.00 sec)

The following is the query to count each row as a serial number:

mysql> select @globalVar:=@globalVar+1 RowCount,tblColumns.*
   -> from (select @globalVar:=0) initializeGlobalVariable,addColumnToCountAsSerialNumber tblColumns;

The following is the output displaying the count in a new column “RowCount”:

+----------+------+-------+------+--------+
| RowCount | Id   | Name  | Age  | Salary |
+----------+------+-------+------+--------+
|        1 |   10 | John  |   23 |   8576 |
|        2 |   12 | Carol |   21 |   4686 |
|        3 |    9 | Mike  |   22 |  38585 |
|        4 |   15 | Sam   |   25 |  38586 |
|        5 |   20 | Bob   |   26 |  43544 |
|        6 |   39 | Larry | 29   | 485886 |
+----------+------+-------+------+--------+
6 rows in set (0.06 sec)
raja
Published on 25-Feb-2019 11:33:17
Advertisements