SQL Group By

SQL Group By clause is used along with select statement to group identical
data.

Group by is used typically with aggregate functions like count,max,avg,sum,min, etc.
In this case it will return one row for each group.

It can group more than one column.

In a query using Group by, Column names other than that in aggregate function
should present in Group by clause.

In a Select query it is used after the where condition and before the Order by Clause.

SQL Having Clause requires Group by to be present in the query.

Let me explain some examples of various Group By statements of  SQL.

General syntax is : 

SELECT column-names FROM table-name WHERE condition
GROUP BY column-names

Suppose there are two tables named Department and Employee with data as follows:

Department :

Employee :

An example of Group by with where condition is :

SELECT Dept_code,name FROM Employee WHERE Salary>4000 
GROUP BY Dept_code,Name

Output :

Syntax with Aggregate function :

SELECT column-name1, fun(column_name2) FROM table-name 
GROUP BY column-name1

An example for this is :

SELECT Dept_code,count(name) AS count FROM Employee
GROUP BY Dept_Code

Output :

Another example is :

SELECT Dept_Code, SUM(SALARY) as 'Total Salary' FROM Employee
GROUP BY Dept_Code

Output :

Another example with Join :

SELECT Dept_name,name FROM Employee E INNER JOIN Department D 
ON E.Dept_code=D.Dept_code
WHERE Salary>2000 GROUP BY Dept_name,name

Output :

Syntax with Order By Clause :

SELECT column-names FROM table-name WHERE condition
GROUP BY column-names
ORDER BY column-names

An example is :

SELECT Dept_code,name FROM Employee WHERE Salary>4000 
GROUP BY Dept_code,name
ORDER BY name

Output :

Having Clause :

Having clause is used for filtering records with summarized Group By results.

The difference between Having and Where condition is that Where condition

filters by applying condition for each record where as Having applies

condition for Aggregate function.

Having Clause in a query must have a Group By clause.

Syntax for Having Clause is :

SELECT column-names FROM table-name WHERE condition
GROUP BY column-names
HAVING condition.

An example is :

SELECT Dept_code,count(name) AS count FROM Employee 
GROUP BY Dept_code
HAVING COUNT(name)>1 

Output  :

Syntax for Having Clause with Order By :

SELECT column-names FROM table-name WHERE condition
GROUP BY column-names
HAVING condition
ORDER BY column-names

An example is :

SELECT Dept_code,count(name) AS count FROM Employee 
GROUP BY Dept_code
HAVING COUNT(name)>1 ORDER BY Dept_code desc

Output  :