SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

SQL GROUP BY CLAUSE

The GROUP BY clause is used to arrange similar records into the groups in the Structured Query Language queries. The records are arranged with the help of functions which is equivalent to each other.

We can use the GROUP BY clause with the SELECT query in the Structured Query Language. We placed the GROUP BY clause after the WHERE clause and before the ORDER BY clause in the SQL queries.

The syntax for the SQL GROUP BY clause is:

SELECT  * FROM Table_Name GROUP BY Column_Name;

The above syntax will select all the data or records from the table name mentioned, and it will arrange all those data or records in the group by column name.

The syntax for SQL GROUP BY clause with a specific column is given below:

SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name__4, Column_Name__5 FROM Table_Name GROUP BY Column_Name;

The above syntax will display records of selective columns only and arrange those column data in the group.

The GROUP BY clause specially used with the aggregate function i.e. max(), min(), avg(), sum(), count() to group result by one or more than one columns.

The below syntax is used by the SQL GROUP BY clause using Aggregate Functions is as follows:

SELECT Column_Name_1, Function_Name(Column_Name) FROM Table_Name GROUP BY Column_Name;

Let’s understand the SQL GROUP BY Clause with the help of an example.

Consider the already existing table with the following data:

Table Name: Employee_Details

E_IdE_NameE_SalaryE_CityDesignationAge
1001Kirti Kirtane60000MumbaiProject Manager26
1002Akash Vani40000PuneSystem Engineer22
1003Anupam Mishra55000HyderabadProject Manager25
1004Anuj Rawat45000HyderabadSoftware Tester25
1005Akanksha Yadav42000PuneAssociate Software Developer23
1006Bhavesh Wani50000MumbaiAssociate Software Developer24
2001Sakshi Sharma40000BangaloreSystem Engineer23
2002Megha Ghatole45000BangaloreSoftware Tester24
2003Surbhi Nahar60000PuneProject Manager26
2004Supriya Shende55000MumbaiSoftware Developer25
2005Prachi Sharma52000HyderabadSoftware Developer24
2006Purva Dhandekar50000BangaloreSoftware Tester23
3001Shruti Deshpande60000PuneProject Manager26
3002Rohit Nikam40000HyderabadSystem Engineer23
3003Sahil Jain50000MumbaiSoftware Developer24

Example 1: Write a query to display employee’s details group by age column

SELECT * FROM Employee_Details GROUP BY Age;

The above query will display all the records of the Employee_Details table group by age column.

The output of the above query is as follows:

E_IdE_NameE_SalaryE_CityDesignationAge
1002Akash Vani40000PuneSystem Engineer22
1005Akanksha Yadav42000PuneAssociate Software Developer23
1006Bhavesh Wani50000MumbaiAssociate Software Developer24
1003Anupam Mishra55000HyderabadProject Manager25
1001Kirti Kirtane60000MumbaiProject Manager26
SQL GROUP BY CLAUSE

Example 2: Write a query to display employee’s details group by Salary, and Designation column

SELECT * FROM Employee_Details GROUP BY E_Salary, Designation;

The above query will display all the records of the Employee_Details table group by Salary and Designation column.

The output of the above query is as follows:

E_IdE_NameE_SalaryE_CityDesignationAge
1002Akash Vani40000PuneSystem Engineer22
1005Akanksha Yadav42000PuneAssociate Software Developer23
1004Anuj Rawat45000HyderabadSoftware Tester25
1006Bhavesh Wani50000MumbaiAssociate Software Developer24
3003Sahil Jain50000MumbaiSoftware Developer24
2006Purva Dhandekar50000BangaloreSoftware Tester23
2005Prachi Sharma52000HyderabadSoftware Developer24
1003Anupam Mishra55000HyderabadProject Manager25
2004Supriya Shende55000MumbaiSoftware Developer25
1001Kirti Kirtane60000MumbaiProject Manager26
SQL GROUP BY CLAUSE

Example 3: Write a query to display employee’s details group by Salary column in the ascending order by Employee name.

SELECT * FROM Employee_Details GROUP BY E_Salary ORDER BY E_Name;

The above query will display all the records of the Employee_Details table group by Salary column in the ascending order by employee name.

The output of the above query is:

E_IdE_NameE_SalaryE_CityDesignationAge
1005Akanksha Yadav42000PuneAssociate Software Developer23
1002Akash Vani40000PuneSystem Engineer22
1004Anuj Rawat45000HyderabadSoftware Tester25
1003Anupam Mishra55000HyderabadProject Manager25
1006Bhavesh Wani50000MumbaiAssociate Software Developer24
1001Kirti Kirtane60000MumbaiProject Manager26
2005Prachi Sharma52000HyderabadSoftware Developer24
SQL GROUP BY CLAUSE

Now, let’s understand the GROUP BY clause using the aggregate function with the help of an example

Consider the already existing table with the following data:

Table Name:  Diploma_Students

Student_IdStudent_NameFirst_SemSecond_SemThird_SemFourth_SemFifth_SemSixth_SemTotalDepartment_Id
202111Vaishnavi Patil949188859592916
202112Vaibhav Lokhande859092808582862
202113Yash Dhull908894878590893
202114Sonali Patole959092889290914
202115Axar Patel858082869284851
202116Meena Mishra787580748577783
202117Mahesh Kumbhar758075788076775
202118Sakshi Patil807874788077782
202119Sopan Bhore706875758080752
202220Prajwal Lokhande808585757880814
202221Anuja Wanare858886828485857
202222Venkatesh Iyer908987909291903
202223Anushka Sen707571748078751
202224Aakash Jain807572748580787
202225Akshay Agarwal858078889082847
202226Shwetali Bhagwat908085889080866
202227Mayuri Wagh808085808285824
202228Utkarsh Rokade858080908484845
202229Manthan Koli857584788280812
202230Mayur Jain808887909290881

Example 1: Write a query to display the total number of student percentages scored in the First-Semester, and Second-Semester column GROUP BY First-Semester, and Second-Semester column

SELECT Count(Student_Name)  AS 'Total Number', First_Sem, Second_Sem FROM Diploma_Student GROUP BY First_Sem, Second_Sem;

The above query displays how many students scored percentages in the first-semester, second-semester GROUP BY first-semester, and second-semester.

The output of the above query is as follows:

Total NumberFirst_SemSecond_Sem
17068
17075
17580
17875
18075
18078
18080
18085
18088
18575
38580
18588
18590
19080
19088
19089
19491
19590
SQL GROUP BY CLAUSE

Example 2: Write a query to sum the student total column percentage group by Department id.

SELECT SUM(Total) AS 'Total', Department_Id FROM Diploma_Student GROUP BY Department_Id;

We summed the student total column group by department id in the above query.

The output of the above query is as follows:

TotalDepartment_Id
2481
3202
2573
2544
1615
1776
2477
SQL GROUP BY CLAUSE

Example 3: Write a query to display the maximum total column percentage of the student GROUP BY Department id.

SELECT MAX(Total) as 'Maximum Percentage', Department_Id FROM Diploma_Student GROUP BY Department_Id;

In the above query, we displayed the maximum student percentage of the total column GROUP BY Department Id

The output of the above query is as follows:

Maximum PercentageDepartment_Id
881
862
903
914
845
916
857
SQL GROUP BY CLAUSE

Example 4: Write a query to display the minimum first-semester column percentage of the student GROUP BY Department id.

SELECT MIN(First_Sem) as 'Minimum Percentage', Department_Id FROM Diploma_Student GROUP BY Department_Id;

In the above query, we displayed the minimum student percentage of the first-semester column GROUP BY Department Id

The output of the above query is as follows:

Minimum PercentageDepartment_Id
701
702
783
804
755
906
807
SQL GROUP BY CLAUSE



ADVERTISEMENT
ADVERTISEMENT