SQL GROUP BY Clause

The Group by clause is used to arrange and sort data into group. To use this group by clause, you must have to use order by clause as well. It also used with aggregate functions like SUM, AVG, COUNT, MIN and MAX.

You may also Order By clause with Group By to arrange and sort the result set. In following examples, we will use SUM function to understand how it works with Group By clause.

Let’s understand each with syntax and examples.

MS SQL

Syntax:

SELECT COLUMN1, COLUMN2, COLUMN3, ...
FROM TABLE_NAME
WHERE Condition
GROUP BY COLUMN1, COLUMN2
ORDER BY COLUMN1, COLUMN2;

Example:

Original Data in table called “Employee”


Id Employee Code First Name Last name Gender Birth Date Expense
1 EMP_1001 John Peter Male 10-11-1982 200
2 EMP_1002 Josh Wills Male 09-07-1980 250
3 EMP_1003 Jeremy Parkar Female 08-09-1990 150
4 EMP_1004 Aidan Walls Male 05-03-1989 300
5 EMP_1005 Adam Mondry Male 02-06-1991 100


SELECT FirstName, SUM(Expense) FROM Employee
GROUP BY Expense

Data displayed from table “Employee” after above Sql Query executed


First Name Expense
John 200
Josh 250
Jeremy 150
Aidan 300
Adam 100


Now, lets add some duplicate records in above table and use similar sql statement to display the result set.


Id Employee Code First Name Last name Gender Birth Date Expense
1 EMP_1001 John Peter Male 10-11-1982 200
2 EMP_1002 Josh Wills Male 09-07-1980 250
3 EMP_1003 Jeremy Parkar Female 08-09-1990 150
3 EMP_1003 Jeremy Parkar Female 08-09-1990 120
4 EMP_1004 Aidan Walls Male 05-03-1989 300
4 EMP_1004 Aidan Walls Male 05-03-1989 250
5 EMP_1005 Adam Mondry Male 02-06-1991 100
5 EMP_1005 Adam Mondry Male 02-06-1991 70

So, from above records, lets find the total expense for each employee by their first name.


SELECT FirstName, SUM(Expense) FROM Employee
GROUP BY Expense

Data displayed from table “Employee” after above Sql Query executed


First Name Expense
John 200
Josh 250
Jeremy 270
Aidan 550
Adam 170

Group By with Order By:

Original Data in table called “Employee”


Id Employee Code First Name Last name Gender Birth Date Expense
1 EMP_1001 John Peter Male 10-11-1982 200
2 EMP_1002 Josh Wills Male 09-07-1980 250
3 EMP_1003 Jeremy Parkar Female 08-09-1990 150
4 EMP_1004 Aidan Walls Male 05-03-1989 300
5 EMP_1005 Adam Mondry Male 02-06-1991 100

Now, let’s find out how many total Male and Female employee are there in result set.


SELECT COUNT(Id) as Total, Gender
FROM Employee
GROUP BY Gender
ORDER BY COUNT(Id) DESC;

Data displayed from table “Employee” after above Sql Query executed


Total Gender
4 Male
1 Female

So, in above sql statement we used Group By and Order By to get how many male and female employee there.