SQL GROUP BY - SQL Tutorial
The SQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).
The best way to explain how and when to use the SQL GROUP BY statement is by example, and that’s what we are going to do.
Consider the following database table called EmployeeHours storing the daily hours for each employee of a factious company:
| Employee | Date | Hours |
| John Smith | 5/6/2004 | 8 |
| Allan Babel | 5/6/2004 | 8 |
| Tina Crown | 5/6/2004 | 8 |
| John Smith | 5/7/2004 | 9 |
| Allan Babel | 5/7/2004 | 8 |
| Tina Crown | 5/7/2004 | 10 |
| John Smith | 5/8/2004 | 8 |
| Allan Babel | 5/8/2004 | 8 |
| Tina Crown | 5/8/2004 | 9 |
If the manager of the company wants to get the simple sum of all hours worked by all employees, he needs to execute the following SQL statement:
|
SELECT SUM (Hours) FROM EmployeeHours |
But what if the manager wants to get the sum of all hours for each of his
employees?
To do that he need to modify his SQL query and use the SQL GROUP BY statement:
|
SELECT Employee, SUM (Hours) FROM EmployeeHours GROUP BY Employee |
The result of the SQL expression above will be the following:
| Employee | Hours |
| John Smith | 25 |
| Allan Babel | 24 |
| Tina Crown | 27 |
As you can see we have only one entry for each employee, because we are grouping by the Employee column.
The SQL GROUP BY clause can be used with other SQL aggregate functions, for example SQL AVG:
|
SELECT Employee, AVG(Hours) FROM EmployeeHours GROUP BY Employee |
The result of the SQL statement above will be:
| Employee | Hours |
| John Smith | 8.33 |
| Allan Babel | 8 |
| Tina Crown | 9 |
In our Employee table we can group by the date column too, to find out what is the total number of hours worked on each of the dates into the table:
|
SELECT Date, SUM(Hours) FROM EmployeeHours GROUP BY Date |
Here is the result of the above SQL expression:
| Date | Hours |
| 5/6/2004 | 24 |
| 5/7/2004 | 27 |
| 5/8/2004 | 25 |