SQL Group by column name clause is used to give aggregate records in database table. Group by keyword is used to give records in same column rows in grouped manner. Group by can be used on one column name and more than one column name in SQL query. group by can be implemented in MySQL, oracle, db2, Microsoft SQL server.
SQL select Group by query example explains how to use select Group by query in SQL.
Table class_name with all records without desired column name sorting
+---------+-------------+------------+------------+ | Roll_No | Name | Class_Name | Year_Start | +---------+-------------+------------+------------+ | 1 | Amit Mishra | B.Science | 2005 | | 2 | Robot | B.Tech | 2005 | | 3 | Rohan | M.Tech | 2005 | | 4 | Vivek | B.Tech | 2009 | | 5 | Chander | B.Science | 2009 | | 6 | Deepak | B.Science | 2009 | +---------+-------------+------------+------------+
Group by
SELECT * FROM class_name c group by Class_Name;
Output
+---------+-------------+------------+------------+ | Roll_No | Name | Class_Name | Year_Start | +---------+-------------+------------+------------+ | 1 | Amit Mishra | B.Science | 2005 | | 2 | Robot | B.Tech | 2005 | | 3 | Rohan | M.Tech | 2005 | +---------+-------------+------------+------------+
this SQL query returns unique column name (class_name) rows only from table.
SELECT Roll_No, Name, Class_Name, count(Class_Name) FROM class_name c group by Class_Name;
Output
+---------+-------------+------------+-------------------+ | Roll_No | Name | Class_Name | count(Class_Name) | +---------+-------------+------------+-------------------+ | 1 | Amit Mishra | B.Science | 3 | | 2 | Robot | B.Tech | 2 | | 3 | Rohan | M.Tech | 1 | +---------+-------------+------------+-------------------+
this SQL query returns unique column name (class_name) rows only from table and it shows count of unique column name.
group by on more than one column name
SELECT * FROM class_name c group by Class_Name,Year_Start;
Output
+---------+-------------+------------+------------+ | Roll_No | Name | Class_Name | Year_Start | +---------+-------------+------------+------------+ | 1 | Amit Mishra | B.Science | 2005 | | 5 | Chander | B.Science | 2009 | | 2 | Robot | B.Tech | 2005 | | 4 | Vivek | B.Tech | 2009 | | 3 | Rohan | M.Tech | 2005 | +---------+-------------+------------+------------+
this SQL query returns, unique column name rows only with two columns
* indicates fetch all columns records from table.
Loading ...
Link to Us