Group By Clause Made Easy

At times using Group By Clause can be tricky. Understanding the basic concept of Group By Clause is very important before using it in any SQL Query.  Group By clause will group the result returned from a Select statement by one or more column depending on how many column your select statement includes.

SQL  SYNTAX

SELECT <column_name>, sum <expression>
From <table name>
GROUP BY <column_name>

 ORACLE SYNTAX:

SELECT <column list>, <group by function>
FROM <table name>
WHERE <conditions>
GROUP_BY <column list>
HAVING <group by function condition>

Let me provide you an example of simple Group By Clause before moving into a complex query structure. We have a table name employee that stores DeptId, Employee First Name and Last Name. Now, we want a count of employee for each department. To get the desired result, we use Group by SQL statements in our query. First, let me show you what we have in our Employee Table.

Select * from employee;

Result:











Next, Let me show you the query to display the number of employee for each Department.

SELECT DeptID , COUNT(EmpId) AS EmployeeCount
FROM employee
Group By DeptID;

Result:

The above query is a  simple Group By Clause, Now, your manager comes to you and ask for the department name instead of the dept ID. To get the required output, you’ll have to write the query that selects the Department Name and Counts the Employee Id assigned to that department and Groups by Department Name.

 Before we begin, lets see what we have in the Department Table.

Select * from Department;

Result:

This is how your query will look like 

Select Department.DepartmentName, Count (Employee.EmpId)  as  EmployeeCount
From  Department
LEFT JOIN employee
On Department.DepartmentID = employee.DeptID
Group By DepartmentName;

Result:

Here, I used Left join to return all the Departments in my result set regardless of  whether the employee is assigned to the department or not (assuming that there might be a department that was recently established without any employee assigned to it).

Now, you provided your result set to your Manger and he comes back to you and says I think I would like to know Employees First Name and Last Name too. Dang it, why don't you ask everything at once? Now to provide that info requested, you will write a query as below.

Select Deparment.DepartmentName, employee.[First Name] as FirstName, employee.[Last Name] as LastName
From  Department
LEFT outer JOIN employee
On Department.DepartmentID = employee.DeptID
Group By Department.DepartmentName, employee.[First Name] , employee.[Last Name]

Result:

Group by Clause includes all the columns that are being selected which are very important. If you fail to include one of the columns then SQL will throw an error.  Imagine, you forget to include the Last Name column in your group by clause above. Below is the error SQL Server will throw.

Error Message:

Msg 8120, Level 16, State 1, Line 1

Column 'employee.Last Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.