>> GROUP BY , GROUP BY HAVING clauses in SQL

GROUP BY , GROUP BY HAVING  clauses in SQL:

GROUP BY is used in aggregate/group functions like SUM, AVG , MAX , MIN , COUNT() etc.

To get SUM or AVG or MAX or MIN or COUNT etc. of  data in a group , we use group by clause.

Let's take below table , EMP_INFO :

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	HIREDATE
DAVID	MARKETING	2000	CHICAGO	MANAGER	IL	12-Dec-00
RICHARD	SALES	1000	BALTIMORE	SALESMAN	MD	1-Jan-08
SMITH	RESOURCES	1500	DALLAS	HR	TX	5-Jan-06
RAM	RESOURCES	1200	DALLAS	ASSISTANT	TX	5-Jun-06
GOPI	SALES	1200	AUSTIN	MARKETER	TX	9-Feb-08
BRITNI	TECHNICAL	1800	BALTIMORE	ASSISTANT	MD	1-Feb-09
ADAM	TECHNICAL	2200	BALTIMORE	LEAD	MD	1-Mar-10
MIKE	EXECUTIVE	2500	CHICAGO	DIRECTOR	IL	1-Aug-10
JAMES	MARKETING	1200	RESTON	ACCOUNTANT	VA	20-Aug-14
JULI	EXECUTIVE	2200	ARLINGTON	MANAGER	VA	21-Aug-12

Let's find out the count of employees in each department .

SELECT DEPARTMENT, COUNT(*) FROM EMP_INFO

GROUP BY DEPARTMENT;

DEPARTMENT	COUNT(*)
TECHNICAL	2
RESOURCES	2
MARKETING	2
EXECUTIVE	2
SALES	2

Let's get average salary in each STATE.

SELECT STATE, AVG(SALARY) FROM EMP_INFO
GROUP BY STATE;

STATE	AVG(SALARY)
VA	1700
MD	1666.666667
IL	2250
TX	1300

You can have group by any number of column-combinations as per your requirement.

Syntax :

SELECT COL1, COL2, COL3, ...GRP_FUNCTION()  FROM TABLE_NAME

GROUP BY COL1, COL2, COL3, ...

What ever columns are mentioned in the Select statement before group function, all those columns should be present after the group by clause also, such as :

SELECT COL1, COL2, COL3, GRP_FUNCTION()  FROM TABLE_NAME

GROUP BY COL1, COL2, COL3 ;

We can also put WHERE(filtering the data before group by),  HAVING (filtering the data after group by )  and ORDER BY (to display the output in desired order) , such as :

SELECT COL1, COL2, COL3, GRP_FUNCTION()  FROM TABLE_NAME

WHERE { condition  before group by }

GROUP BY COL1, COL2, COL3 ;

HAVING { condition after group by }

ORDER BY {DESIRED_COLUMN}

Example with SELECT, WHERE, GROUP BY and ORDER BY :

Suppose we want to get max salary of each DEPARTMENT in the states MD and TX . And we want to see the result in the order of STATE.

SELECT STATE, DEPARTMENT, MAX(SALARY) FROM EMP_INFO
WHERE STATE IN ('TX','MD')
GROUP BY STATE, DEPARTMENT
ORDER BY STATE;

STATE	DEPARTMENT	MAX(SALARY)
MD	SALES	1000
MD	TECHNICAL	2200
TX	RESOURCES	1500
TX	SALES	1200

NOTE: When there are more than one values in the where condition we use "IN" instead of "="
For example, if there were only one state in where condition, we use like this :
WHERE STATE = 'TX'.

 Example with SELECT, WHERE, GROUP BY , HAVING and ORDER BY :

Suppose we want to get max salary of each DEPARTMENT in the states MD and TX if the salary is above 1000, and we want to see the result in the order of STATE.

SELECT STATE, DEPARTMENT, MAX(SALARY) FROM EMP_INFO
WHERE STATE IN ('TX','MD')
GROUP BY STATE, DEPARTMENT
HAVING MAX(SALARY) >1000
ORDER BY STATE;

STATE	DEPARTMENT	MAX(SALARY)
MD	TECHNICAL	2200
TX	RESOURCES	1500
TX	SALES	1200

NEXT

Go To Main SQL Page


.