TRENDING TOPICS

×

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"

ID EName ECity Gender Salary
1 Shiv Shukla Mumbai Male 5000
2 John Peater Delhi Male 6000
3 Rumi Gupta Delhi Female 4000
4 Ruhan Khan Lucknow Male 4500
5 Rashmi Deshai Mumbai Female 2000
6 Vikram Yadav Lucknow Male 5000
7 Nitika Rai Delhi Female 1000
8 Dharmendra Patil Lucknow Male 2500

Using GROUP BY Examples

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

Result Look Like

ECity TotalSalary
Mumbai 7000
Delhi 11000
Lucknow 12000

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

ECity Gender TotalSalary
Mumbai Male 5000
Delhi Male 6000
Lucknow Male 12000

© 2021 GDATAMART.COM (All Rights Reserved)