SQL: Group By Clause

The SQL Group By statement is used to organize similar data into groups. The data is further organized using an equivalent function. It means that if multiple rows in a specific column have the same values, they will be grouped.

The SELECT statement is combined with the GROUP BY clause in a SQL query:

• The WHERE clause comes before the GROUP BY clause in SQL.
• The ORDER BY clause comes after the GROUP BY clause in SQL.

Syntax:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

Note: GROUP BY clause is typically used with aggregate functions like SUM, COUNT, AVG, MIN, or MAX to compute summary information for each group.

Example:

Consider an Employee table:

EmpId	FirstName	LastName	Salary	DeptId
1	John	King	33000	1
2	James	Bond	33000	1
3	Neena	Patel	17000	2
4	Lex	De Haan	15000	1
5	Amit	Patel	15000	1
6	Abdul	Kalam	25000	2

• Query to count the number of employees in each department:

SELECT DeptId, COUNT(EmpId) as 'Number of Employees' 
FROM Employee
GROUP BY DeptId;

The ‘No of Employees’ column is an abbreviation for the COUNT(EmpId) column. The query returns the following result.

Output:

DeptId	No of Employees
1	4
2	2

• GROUP BY with WHERE clause:

SELECT EmpId, Salary 
FROM Employee 
WHERE LastName = "Patel"
GROUP BY Salary

The above query is used to select EmpId and Salary with WHERE clause and Group BY clause being on the SALARY column.

Output:

EmpId	Salary
3	17000
5	15000

Note: also read about SQL: ORDER BY Clause

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py