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

How to use GROUP BY clause in SQL

In this SQL article, we will learn about the GROUP BY clause and how to use it in SQL. We will also discuss using the GROUP BY clause with the WHERE clause.

What is the GROUP BY clause?

The GROUP BY clause is an SQL clause used in the SELECT statement to manage the same records of a column in the group using SQL functions.

Syntax of GROUP BY clause:

SELECT columnname1, columnname2, columnname3 FROM tablename GROUP BY columnname;

We can use multiple columns from the table in the GROUP BY clause.

There are some steps, we have to learn for how to use the GROUP BY clause in the SQL query:

1. Create a new database or use an existing database by selecting the database using the USE keyword followed by the database name.

2. Create a new table inside the selected database, or you can use an already created table.

3. If the table is newly created, insert the records in the newly created database using the INSERT query and view the inserted data using the SELECT query without the GROUP BY clause.

4. Now, we are ready to use the GROUP BY clause in the SQL queries.

Step 1: Create a new database or use an already created database.

I have already created a database. I will use my existing created database name, Company.

USE Company;

Company is the database name.

Those who didn’t have created a database follow the below query to create the database:

CREATE DATABASE database_name;

After creating the database, select the database using the USE keyword followed by the database name.

Step 2: Create a new table or use already existing table:

I have already created a table. I will use the existing table named Employees.

To create the new tables, follow the below CREATE TABLE syntax:

CREATE TABLE table_name(

columnname1 datatype(column size),

columnname2 datatype(column size),

columnname3 datatype(column size)

);

Step 3: Insert the records in the newly created table using the INSERT query and view the records using the SELECT query.

Use the following syntax to insert new records in the table:

INSERT INTO table_name VALUES(value1, value2, value3);

Use the following syntax to view the records from the table:

SELECT * FROM table_name;

The following query will display the records of Employees:

SELECT * FROM Employees;

The output of the above SELECT query is:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4
5002SANKETCHAUHAN70000HYDERABADJAVA3
5003ROSHANNEHTE48500CHANDIGARHC#5
6001RAHULNIKAM54500BANGALORETESTING4
6002ATISHJADHAV60500BANGALOREC#5
6003NIKITAINGALE65000HYDERABADORACLE1

Step 4: We are ready to use the GROUP BY clause in the queries

We will now take deep dive into the GROUP BY clause with the help of examples

Example 1: Write a query to display the employee records group by city.

SELECT * FROM EMPLOYEES GROUP BY CITY;

The above query displays the employees' records where an employee from the same city will be considered one group. For example, if there are 10 employees records in table where 3 are from Pune city, 3 are from Mumbai city, 2 are from Hyderabad and Bangalore, then the above query will group Pune city employee Mumbai city employee as one record, and so on.

The output of the above query:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
6001RAHULNIKAM54500BANGALORETESTING4
2001PRACHISHARMA55500CHANDIGARHORACLE1
4003RUCHIKAAGARWAL60000DELHIORACLE1
5002SANKETCHAUHAN70000HYDERABADJAVA3
1003NIKHILVANI50500JAIPURFMW2
2003RUCHIKAJAIN50000MUMBAI C# 5
1002VAIBHAVSHARMA60000NOIDAC#5
1001VAIBHAVIMISHRA65500PUNEORACLE1
How To Use GROUP BY Clause In SQL

As we can see that the employees' records are grouped by city, and records are displayed in the ascending order by default.  

Example 2: Write a query to display employees' records group by salary in descending order.

SELECT * FROM EMPLOYEES GROUP BY SALARY DESC; 

The above query displays the employees' records where employees with the same salary will be considered one group, and records will display in descending order.

The output of the above query:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
5002SANKETCHAUHAN70000HYDERABADJAVA3
1001VAIBHAVIMISHRA65500PUNEORACLE1
6003NIKITAINGALE65000HYDERABADORACLE 1
4001RAJESHGOUD60500MUMBAITESTING 4
1002VAIBHAVSHARMA60000NOIDAC#5
3003DEEPAMJAUHARI58500MUMBAIJAVA 3
2001PRACHISHARMA55500CHANDIGARHORACLE1
4002ASHWINIBAGHAT54500NOIDAJAVA 3
1003NIKHILVANI50500JAIPURFMW2
2003RUCHIKAJAIN50000MUMBAI C# 5
5003ROSHANNEHTE48500CHANDIGARHC# 5
How To Use GROUP BY Clause In SQL

As we can see, the employees' records are grouped by salary, and records are displayed in descending order, as we mention desc at the end.

Example 3: Write a query to display employees’ records group by salary and city.

SELECT * FROM EMPLOYEES GROUP BY SALARY, CITY;

The above query displays the employees' records where employees with the same salary and city will be considered one group.

For example, suppose the table had 10 employee records. From 10 employees 2 employees' salary and city matches with other two employees and rest six employees salary and city is unmatched then the 6 employees will be considered as 6 separate groups, and 2 employees who match with other 2 employees will be considered as one group. In short, 8 groups will be formed.

The output of the above query:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
5003ROSHANNEHTE48500CHANDIGARHC#5
2003RUCHIKAJAIN50000MUMBAIC#5
1003NIKHILVANI50500JAIPURFMW2
6001RAHULNIKAM54500BANGALORETESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
2001PRACHISHARMA55500CHANDIGARHORACLE1
5001ARCHITSHARMA55500DELHITESTING4
3001PRANOTISHENDE55500PUNEJAVA3
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
6002ATISHJADHAV60500BANGALOREC#5
4001RAJESHGOUD60500MUMBAITESTING4
6003NIKITAINGALE65000HYDERABADORACLE1
1001VAIBHAVIMISHRA65500PUNEORACLE1
5002SANKETCHAUHAN70000HYDERABADJAVA3
How To Use GROUP BY Clause In SQL

As we can see, the employees' records are grouped by salary and city, and records are displayed in ascending order by default. 

Example 4: Write a query to display employees' records by city and department.

SELECT * FROM EMPLOYEES GROUP BY CITY, DEPARTMENT;

The above query displays the employees' records where employees are in the same city, and the department will be considered one group.

The output of the above query:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
6002ATISHJADHAV60500BANGALOREC#5
6001RAHULNIKAM54500BANGALORETESTING4
5003ROSHANNEHTE48500CHANDIGARHC#5
2001PRACHISHARMA55500CHANDIGARHORACLE1
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4
5002SANKETCHAUHAN70000HYDERABADJAVA3
6003NIKITAINGALE65000HYDERABADORACLE1
1003NIKHILVANI50500JAIPURFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
1002VAIBHAVSHARMA60000NOIDAC#5
4002ASHWINIBAGHAT54500NOIDAJAVA3
2002BHAVESHJAIN65500PUNEFMW2
3001PRANOTISHENDE55500PUNEJAVA3
1001VAIBHAVIMISHRA65500PUNEORACLE1
How To Use GROUP BY Clause In SQL

As we can see, the employees' records are grouped by city and department, and records are displayed in ascending order by default. 

Example 5: Write a query to count the list of employees in each department from the employees' table.

SELECT DEPARTMENT, COUNT(DEPARTMENT) FROM EMPLOYEES GROUP BY DEPARTMENT;

The above query displays the count of employees in each department group by the department. Like Six employees work in the HR department, five work in another department.

The output of the above query:

DEPARTMENTCOUNT(DEPARTMENT)
C#4
FMW3
JAVA4
ORACLE4
TESTING3
How To Use GROUP BY Clause In SQL

As we can see, four employees work in C# Department, three work in FMW Department, etc.

Example 6: Write a query to count the list of employees from each city from the employees' table.

SELECT CITY, COUNT(CITY) FROM EMPLOYEES GROUP BY CITY;

The above query displays the count of employees in each city group by city. Like three employees work from Pune city, four work from another city, and so on.

The output of the above query:

CITYCOUNT(CITY)
BANGALORE2
CHANDIGARH2
DELHI2
HYDERABAD2
JAIPUR2
MUMBAI3
NOIDA2
PUNE3
How To Use GROUP BY Clause In SQL

As we can see, two employees work from Bangalore city, three works from Mumbai city, and so on.

Example 7: Write a query to sum the employee salary group by the city.

SELECT CITY, SUM(SALARY) AS SALARY FROM EMPLOYEES GROUP BY CITY;

The above is used to sum the employees' salaries grouped by the city name. For example, for employees from the same city, their salary will be the sum and considered one group. We used the aggregate sum function followed by the salary column for adding salary.

The output of the above query:

CITYSALARY
BANGALORE115000
CHANDIGARH104000
DELHI115500
HYDERABAD135000
JAIPUR101000
MUMBAI169000
NOIDA114500
PUNE186500
How To Use GROUP BY Clause In SQL

As we can see, Bangalore city sum salary is 115000, Chandigarh city sum salary is 104000 which is the addition of different employee salary, but from the city, the same approach is used for each city.

Example 8: Write a query to find the minimum salary from each department.

SELECT DEPARTMENT, MIN(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT;

The above query is used to find the employee's minimum salary from each department. One of the employees from the Java department's salary is 54500, which is the lowest in over the entire java department. The same 48500 is the lowest salary paid to the employee in the C# department.

The output of the above query:

DEPARTMENTMIN(SALARY)
C#48500
FMW50500
JAVA54500
ORACLE55500
TESTING54500
How To Use GROUP BY Clause In SQL

As we can see, 50500 is the lowest salary paid to one of the employees in the FMW department, 55500 is the lowest salary paid to one of the employees in the ORACLE department.

Example 9: Write a query to find the minimum salary from each city.

SELECT CITY, MAX(SALARY) FROM EMPLOYEES GROUP BY CITY;

The above query is used to find the maximum salary from each city. One of the employees from the Pune city salary is 65500 which is the highest in over the entire Pune city, same 60500 is the highest salary paid to the employee in the Mumbai city.

The output of the above query:

CITYMAX(SALARY)
BANGALORE60500
CHANDIGARH55500
DELHI60000
HYDERABAD70000
JAIPUR50500
MUMBAI60500
NOIDA60000
PUNE65500
How To Use GROUP BY Clause In SQL

As we can see, 50500 is the highest salary paid to one of the employees in Jaipur city, 55500 is the highest salary paid to one of the employees in Chandigarh city.



ADVERTISEMENT
ADVERTISEMENT