GROUP BY is used to group together rows that have the same value of a column.
Table: Employee
| EmployeeID |
FirstName |
LastName |
DepartmentID |
City |
Country |
| EMP001 |
Maria
|
Anders |
ACC |
Berlin |
Germany |
| EMP002 |
Ernst
|
Handel |
IT |
Graz |
Austria |
| EMP003 |
Helen
|
Bennett |
ACC |
Cowes |
UK |
| EMP004 |
Paula
|
Wilson |
IT |
Albuquerque |
USA |
| EMP005 |
Wong |
Liu |
HR |
Butte |
USA |
Consider the following SQL statement
SELECT
DepartmentID, COUNT(*) FROM Employee
GROUP BY
DepartmentID |
|
Resultset 1
| DepartmentID |
|
| ACC |
2 |
| HR |
1 |
| IT |
2 |
From the above example, can you see that GROUP BY is totaling up the
number of employee by department.
|
|
The above example returns all DepartmentID in Employee table. How if
we only want to return Departments that have more than 1 employee ? You can do
this by using SQL HAVING clause to filter the records that you want to
consider. For example, if you can use the following SQL statement to return
departments that have more than one employee:
SELECT
DepartmentID, COUNT(*) FROM Employee
GROUP BY DepartmentID
HAVING
COUNT(*) > 1
Resultset 2
.
