Difference between Having Clause and Group by Clause

SQL is a language which consists of different types of clauses in the Select statement like

• Where
• Group by
• Having
• Order by

These clauses are used to retrieve values, sort them, group them, and do a lot of other things. In this article, we will discuss the difference between having clause and group by clause.

What is Having Clause?

The Having clause is used to filter groups of rows based on a condition, which often includes the aggregate functions. It comes after the GROUP BY clause in a SQL query. Unlike the WHERE clause, which filters rows before any groupings is done, the HAVING clause filters groups based on aggregate functions.

The aggregate functions commonly used with the HAVING clause are as follows −

• MAX
• MIN
• COUNT
• SUM
• AVG

Syntax of Having Clause

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Example of Having Clause

The following table will be used for the example of having clause. The table name is Department which is created by using the Create Table statement.

Create Table Department (
   Department varchar (255), Salary (int)
)

Now we will insert values by using the Insert into Statement

Insert into Department (Department, Salary) Values
("HR", 24,000),
("Inventory", 40,000),
("Sales", 45,000),
("IT", 50,000),
("Accounts", 24,000)
)

Select d.Department, AVG(Salary)
From Department d
Group by d.Department
Having AVG(s.Salary) > 10,000

What is Group by Clause?

The group by clause is a clause whose results include rows which are grouped by using one or more columns. The result is in the form of a group. The group by clause is included in the Select statement to get the result.

Syntax of Group by Clause

The syntax of Group by clause is as follows −

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Example of Group by Clause

An example of the group by clause is as follows −

Here is a table named Customers which we will create by using the Create Table statement.

Create Table Customers (
   Customer ID int,
   Customer Name varchar (255),
   Country varchar (255)
)

Now we will insert the values in the Customers table by using Insert into statement.

Insert Into Customers (Customer ID, Customer Name, Country) Values
(1, "Elizabeth", "Belgium"),
(2, "Victoria", "Canada"),
(3, "James", "USA"),
(4, "Ahmed", "Yemen"),
(5, "Joseph", "Ukraine"),
(6, "Antonio", "Belgium")

Select count (Customer ID), country from customers group by country

Difference between Group by clause and Having clause

The difference between the Group by clause and Having clause can be found in the table below −

Conclusion

Group by clause and Having clause are used with the Select statement to retrieve specific rows as values. The Group by clause is used to retrieve a group of rows on the basis of a column or row. The Having clause cannot be used without the Group by clause. The having clause is used to add some specific conditions to restrict the output.

FAQs on Group Clause Vs. Having Clause

1. Can Having clause be used independently?

No! Having clause cannot be used independently. It has to be used with the Group by clause. The having clause restricts the results that is to be retrieved by using the Group by clause. A few conditions are added in the Having clause to restrict the output.

2. What is the syntax of Group by Clause?

The syntax of the group by clause is given below

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

3. What is the syntax of the Having clause?

The syntax of the Having clause is as follows −

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

4. Can the Having clause be used with the where clause?

Yes! The having clause can be used with the Where clause.

5. Which aggregate functions can we use with the Having clause?

The aggregate functions that are used with the Having clause are −

• MAX
• SUM
• AVG