SQL GROUP BY

The SQL GROUP BY statement is used along with the aggregate functions to group the result dataset by one or more database table columns.

SQL GROUP BY Statement Syntax

SELECT ColumnName1, ColumnName2, ... , aggregate_function (ColumnName)
FROM TableName
WHERE predicates
GROUP BY ColumnName1, ColumnName2, ... 

The aggregate_function could be COUNT, SUM, MAX, MIN, AVG, …

SQL GROUP BY Statement Example

Table: DailyHours

EmployeeId EmployeeName Department WorkingDate Hours
203 Mazojys Fxoj Finance 2010-08-29 8
204 Jozzh Lnanyo Finance 2010-08-29 8
302 Jssme Bdnaa Development 2010-08-29 10
303 Dnnaao Errllov Development 2010-08-29 8
304 Tyoysww Osk Development 2010-08-29 9
203 Mazojys Fxoj Finance 2010-08-30 8
204 Jozzh Lnanyo Finance 2010-08-30 9
302 Jssme Bdnaa Development 2010-08-30 10
303 Dnnaao Errllov Development 2010-08-30 10
304 Tyoysww Osk Development 2010-08-30 9

Example 1
We want to find out the total hours for each department:

SELECT Department, SUM(Hours) AS TotalHours
FROM DailyHours
GROUP BY Department

The result will look like:

Department TotalHours
Finance 33
Development 56

Example 2
Sometime we should use the GROUP BY statement on more than one column, for example, we want to find the total hours for each department each day:

SELECT Department, WorkingDate, SUM(Hours) AS TotalHours
FROM DailyHours
GROUP BY Department, WorkingDate

The result will look like:

Department WorkingDate TotalHours
Finance 2010-08-29 16
Finance 2010-08-30 17
Development 2010-08-29 27
Development 2010-08-30 29

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>