HAVING Clause in SQL Server

The HAVING clause in SQL Server is used to filter groups or aggregates based on a condition. It is often used with the GROUP BY clause to apply the condition to each group. For example, you can use the HAVING clause to find the customers who have placed more than 10 orders, or the products that have sold more than 100 units.

The syntax of the HAVING clause is:

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

The HAVING clause can use any aggregate function, such as SUM, COUNT, AVG, MIN, MAX, etc. The HAVING condition can also use logical operators, such as AND, OR, NOT, etc.

Difference between HAVING and WHERE CLAUSE

Both HAVING and WHERE clauses are used to filter the data in SQL queries but they still differ in many aspects which are mentioned below:

HAVINGWHERE
The HAVING clause is used to filter data from groups according to the specified condition.The WHERE clause is used to filter individual content from table according to the specified condition.
HAVING clause is used after the groups are made (Post-filter).WHERE clause is used before the groups are made (Pre-filter).
HAVING clause needs to be executed with the GROUP BY clause.WHERE clause can be executed without the GROUP BY clause.
In SQL queries, the HAVING clause is used after the GROUP BY clause.In SQL queries, the WHERE clause is used before the GROUP BY clause.
HAVING clause can only be used with the SELECT statement for filtering the data.WHERE clause can be used with SELECT, UPLOAD and DELETE statements.
SQL aggregate functions can be used with the HAVING clause in a query.SQL aggregate functions cannot be used with the WHERE clause in a query.
HAVING clause is used in column operations.WHERE clause is used in row operations.
  • The HAVING clause in SQL is used to filter results obtained by the GROUP BY clause based on some specific conditions.
  • The HAVING clause is similar to the WHERE clause but the HAVING clause is used to filter groups while the WHERE clause is used to filter individual content from the table.
  • In a query, the HAVING clause is placed after the GROUP BY clause and before the ORDER BY clause.
  • The HAVING clause can only be used with the SELECT clause.

  • 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):

    SELECT COUNT(CustomerID), Country FROM Customers
    GROUP BY Country HAVING COUNT(CustomerID) > 5
    ORDER BY COUNT(CustomerID) DESC;

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

    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:

    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;