A single MySQL query to combine strings from many rows into a single row and display the corresponding User Id sum in another column?

For this, you can use GROUP_CONCAT(). Use SUM() to add the User Id. Let us first create a table −

mysql> create table DemoTable1960
   (
   StudentId int,
   StudentName varchar(20)
   );
Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1960 values(100,'Chris');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1960 values(101,'Bob');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1960 values(102,'David');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1960 values(103,'Mike');
Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1960;

This will produce the following output −

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
|       100 | Chris       |
|       101 | Bob         |
|       102 | David       |
|       103 | Mike        |
+-----------+-------------+
4 rows in set (0.00 sec)

Here is the query to combine strings from many rows into a single row and also display the sum of User Ids −

mysql> select sum(StudentId),group_concat(StudentName separator '.') as ListOfStudent from DemoTable1960;

This will produce the following output −

+----------------+----------------------+
| sum(StudentId) | ListOfStudent        |
+----------------+----------------------+
|            406 | Chris.Bob.David.Mike |
+----------------+----------------------+
1 row in set (0.00 sec)

AmitDiwan

Published on 31-Dec-2019 07:19:09

• Related Questions & Answers
• Write a single MySQL query to return the ID from the corresponding row which is a NOT NULL value
• MySQL query to combine two columns in a single column?
• MySQL query to sum rows having repeated corresponding Id
• MySQL query to group concat and place data into a single row on the basis of 1 values in corresponding column?
• Display substrings of different length with a single MySQL query and combine the result
• Can we update a row with the highest ID in a single MySQL query?
• How to increment all the rows of a particular column by 1 in a single MySQL query (ID column +1)?
• Return only a single row from duplicate rows with MySQL
• Count and sort rows with a single MySQL query
• Count multiple rows and display the result in different columns (and a single row) with MySQL
• Get values from all rows and display it a single row separated by comma with MySQL
• Insert multiple rows in a single MySQL query
• Sum values of a single row in MySQL?
• MySQL query to merge rows if Id is the same and display the highest corresponding value from other columns
• Get the first 10 rows followed by the syntax to display remaining row records with a single MySQL query