SQL GROUP BY Clause

GROUP BY clause is used to group a selected set of ROWS into a set of summary ROWS by the values of one or more columns or expressions. The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

• GROUP BY used to group the data partition the set of TABLES into groups based on certain criteria.
• Groups are formed on the basis of certain attributes.

GROUP BY Syntax Example

SELECT column_name(s)
FROM table_Name
WHERE [condition]
GROUP BY column_name(s)

Demo Database Table

First create a table, the table name is: "tblEmployee"

Using GROUP BY Examples

SELECT ECity, SUM(Salary) AS TotalSalary
FROM tblEmployee
GROUP BY ECity

Result Look Like

NOTE - If you omit, the GROUP BY clouse and try to execute the query, get an error:

For Example

SELECT ECity, SUM(Salary) AS TotalSalary
FROM tblEmployee

Error - Column 'tblEmployee.ECity' is invalid in the select list becouse it is not contained in either an aggregate function or the GROUP BY clause.

Filtering Groups [WHERE & HAVING]

WHERE clause is used to filter rows before aggregation, where as HAVING clause is used to filter groups after aggregation.

SELECT Gender, ECity, SUM(Salary) AS TotalSalary
FROM tblEmployee 
WHERE Gender='Male'
GROUP BY ECity

SELECT ECity, SUM(Salary) AS TotalSalary
FROM tblEmployee 
GROUP BY ECity
HAVING Gender='Male'

Result Look Like