Select aggregate function and all other columns in MySQL
Let us first create a table −
mysql> create table DemoTable1621
-> (
-> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> StudentName varchar(20),
-> StudentMarks int
-> );
Query OK, 0 rows affected (1.69 sec)
Insert some records in the table using insert command −
mysql> insert into DemoTable1621(StudentName,StudentMarks) values('Chris',45);
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable1621(StudentName,StudentMarks) values('Bob',78);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1621(StudentName,StudentMarks) values('David',89);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable1621(StudentName,StudentMarks) values('Adam',87);
Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −
mysql> select * from DemoTable1621;
This will produce the following output −
+-----------+-------------+--------------+
| StudentId | StudentName | StudentMarks |
+-----------+-------------+--------------+
| 1 | Chris | 45 |
| 2 | Bob | 78 |
| 3 | David | 89 |
| 4 | Adam | 87 |
+-----------+-------------+--------------+
4 rows in set (0.00 sec)
Following is the query to select aggregate function −
mysql> select tblData.*,( select max(StudentMarks) from DemoTable1621) as MaximumMarks from DemoTable1621 tblData;
This will produce the following output −
+-----------+-------------+--------------+--------------+
| StudentId | StudentName | StudentMarks | MaximumMarks |
+-----------+-------------+--------------+--------------+
| 1 | Chris | 45 | 89 |
| 2 | Bob | 78 | 89 |
| 3 | David | 89 | 89 |
| 4 | Adam | 87 | 89 |
+-----------+-------------+--------------+--------------+
4 rows in set (0.00 sec)
Published on 08-Nov-2019 11:12:02
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP