SQL HAVING Clause

❮ PreviousNext ❯

---

SQL HAVING clause in SQL is used in conjunction with the GROUP BY clause to filter the results of a query after grouping and aggregation have been applied. It allows you to specify additional conditions that must be met by the groups of rows, based on the result of aggregate functions, before the final result set is returned.

The syntax for using HAVING clause is as follows:

SELECT column1, column2, ..., columnN, 

aggregate_function(columnX)

FROM table_name

GROUP BY column1, column2, ..., columnN

HAVING condition;

Where:

• column1, column2, ..., columnN are the columns that you want to group by.
• aggregate_function(columnX) is the aggregate function that you want to apply to a specific column columnX after grouping the data.
• table_name is the name of the table from which you want to retrieve data.
• condition is the condition that specifies the filter criteria for the groups of rows. This can include comparisons, logical operators, and aggregate functions.

The HAVING clause is similar to the WHERE clause, but it operates on the result of the grouping and aggregation, while the WHERE clause operates on the original rows before grouping.

Here's an example of how you might use HAVING to filter the results of a grouped and aggregated query:

SELECT product_name, SUM(quantity) as total_quantity

FROM sales

GROUP BY product_name

HAVING SUM(quantity) > 1000;

In this example, the product_name column is used as the grouping column, and the SUM aggregate function is used to calculate the total quantity for each unique product name in the sales table. The HAVING clause then filters the groups of rows and only returns the ones where the total quantity is greater than 1000. 

SQL HAVING Examples 

The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:

 

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

HAVING COUNT(CustomerID) > 5; 

The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers): 

Example 

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

HAVING COUNT(CustomerID) > 5

ORDER BY COUNT(CustomerID) DESC;

More HAVING Examples 

The following SQL statement lists the employees that have registered more than 10 orders: 

Example 

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders

FROM (Orders

INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)

GROUP BY LastName

HAVING COUNT(Orders.OrderID) > 10; 

The following SQL statement lists if the employees "Davolio" or "Fuller" have registered more than 25 orders: 

Example 

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders

FROM Orders

INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID

WHERE LastName = 'Davolio' OR LastName = 'Fuller'

GROUP BY LastName

HAVING COUNT(Orders.OrderID) > 25;

 

❮ PreviousNext ❯

---