What is GROUP BY and HAVING clause in SQL?

The HAVING Clause enables you to specify conditions that filter which group results appear in the results.

The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax

The following code block shows the position of the HAVING Clause in a query.

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following code block has the syntax of the SELECT statement including the HAVING clause −

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Example

Consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is an example, which would display a record for a similar age count that would be more than or equal to 2.

The SQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.

Syntax

The syntax for the HAVING clause in SQL is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;

Parameters or Arguments

expression1, expression2, ... expression_nExpressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause near the end of the SQL statement.aggregate_functionThis is an aggregate function such as the SUM, COUNT, MIN, MAX, or AVG functions.aggregate_expressionThis is the column or expression that the aggregate_function will be used on.tablesThe tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.WHERE conditionsOptional. These are the conditions for the records to be selected.HAVING conditionThis is a further condition applied only to the aggregated results to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.

Example - Using SUM function

Let's look at a SQL HAVING clause example that uses the SQL SUM function.

You could also use the SQL SUM function to return the name of the department and the total sales (in the associated department). The SQL HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

Example - Using COUNT function

Let's look at how we could use the HAVING clause with the SQL COUNT function.

You could use the SQL COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The SQL HAVING clause will filter the results so that only departments with more than 10 employees will be returned.

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;

Example - Using MIN function

Let's next look at how we could use the HAVING clause with the SQL MIN function.

You could also use the SQL MIN function to return the name of each department and the minimum salary in the department. The SQL HAVING clause will return only those departments where the minimum salary is greater than $35,000.

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) > 35000;

Example - Using MAX function

Finally, let's look at how we could use the HAVING clause with the SQL MAX function.

For example, you could also use the SQL MAX function to return the name of each department and the maximum salary in the department. The SQL HAVING clause will return only those departments whose maximum salary is less than $50,000.

In Sql Server, we have group by clause for grouping the records of the database table according to our need. We use having clause to filter data that we get from group by clause.Having clause operates only on group by clause means to use having clause we need to use group by clause first. Lets go through both the clauses.

Group By Clause

Group By clause is used for grouping the records of the database table(s).This clause creates a single row for each group and this process is called aggregation. To use group by clause we have to use at least one aggregate function in Select statement. We can use group by clause without where clause.

Syntax for Group By Clause

 SELECT Col1, Col2, Aggreate_function 
FROM Table_Name 
WHERE Condition 
GROUP BY Col1, Col2 

Let's see how the Group By clause works. Suppose we have a table StudentMarks that contains marks in each subject of the student.

 Create table StudentMarks
(
 st_RollNo int ,
 st_Name varchar(50),
 st_Subject varchar(50),
 st_Marks int
)
--Insert data in StudentMarks table
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(1,'Mohan','Physics',75);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(1,'Mohan','Chemistry',65);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(1,'Mohan','Math',70);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks) values(2,'Vipul','Physics',70);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(2,'Vipul','Chemistry',75);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks) values(2,'Vipul','Math',60);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(3,'Jitendra','Physics',85);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(3,'Jitendra','Chemistry',75);
insert into StudentMarks(st_RollNo,st_Name,st_Subject,st_Marks)
values(3,'Jitendra','Math',60);
--Now see data in table
select * from StudentMarks 

 -- Group By clause without where condition
SELECT st_Name, SUM(st_Marks) AS 'Total Marks'
FROM StudentMarks
GROUP BY st_Name; 

 -- Group By clause with where condition
SELECT st_Name, SUM(st_Marks) AS 'Total Marks'
FROM StudentMarks
where st_Name='Mohan'
GROUP BY st_Name; 

 -- Group By clause to find max marks in subject
SELECT st_Subject,max(st_Marks) AS 'Max Marks in Subject'
FROM StudentMarks
GROUP BY st_Subject; 

Having Clause

This clause operates only on group rows of table(s) and act as a filter like as where clause. We use having clause to filter data that we get from group by clause. To use having clause we need to use group by clause first.

 -- Having clause without where condition
SELECT st_Name, SUM(st_Marks) AS 'Students Scored > 205'
FROM StudentMarks
GROUP BY st_Name
HAVING SUM(st_Marks) > 205 

 -- Having clause with where condition
SELECT st_Name, SUM(st_Marks) AS 'Students Scored > 205'
FROM StudentMarks
where st_RollNo between 1 and 3
GROUP BY st_Name
HAVING SUM(st_Marks) > 205 

Note

1. To use Group By Clause, we need to use at least one aggregate function

2. All columns that are not used by aggregate function(s) must be in the Group By list

3. We can use Group By Clause with or without Where Clause.

4. To use Having Clause, we have to use Group By Clause since it filters data that we get from Group By Clause

Summary

In this article I try to explain Group By and Having Clause. I hope after reading this article you are familiar with Group By and Having Clause. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

What is GROUP BY clause in SQL?

What is HAVING clause in SQL with example?

What is difference between WHERE HAVING and GROUP BY?

Why we use HAVING with GROUP BY in SQL?