SQL HAVING Clause

The SQL HAVING clause was added to use with aggregate functions insteadof SQL WHERE clause.

The SQL HAVING clause enables you to define conditions that filter which group results appear in the final results.

In the SQL HAVING clause statement must contains atleast one aggregate function and SQL GROUP BY clause in the SQL query.

The SQL HAVING clause can be applied in any valid SQL GROUP BY statement.

The HAVING clause in SQL is used to add a filter to the GROUP BY result depending on a condition. The criteria are of the Boolean type, which means they make use of logical operators (AND, OR). The WHERE keyword failed when used with aggregate expressions, hence this clause was added to SQL. In SQL, the having clause is fairly common. HAVING is similar to WHERE in that it aids in the application of criteria, but it works with groups. The HAVING clause comes into play when you want to filter a group.

Some important points:

• The SQL HAVING clause specifies a group or aggregate search condition.
• The having clause is commonly used in huge data reports.
• Only constants are allowed in the syntax expression.
• ORDER BY should be placed after any HAVING clauses in the query.
• The HAVING Clause is used to perform column operations.
• The GROUP BY clause is used to group together necessary data.

HAVING is typically used in a GROUP BY clause, but it can also be used as a WHERE clause if you aren't using a GROUP BY clause. With SQL SELECT, you must utilise HAVING.

The WHERE clause applies conditions to the columns that have been selected, whereas the HAVING clause applies conditions to the groups generated 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

In a query, the HAVING clause must come after the GROUP BY clause and, if used, before the ORDER BY clause. The syntax of the SELECT statement, including the HAVING clause, is shown in the following code block:

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

Example 1: SQL HAVING clause

The following query display cust_country and number of customers for the same grade for each cust_country, with the following condition:

Number of customer for a same 'grade' must be more than 2,

SELECT cust_country,COUNT(grade) 
FROM customer 
GROUP BY cust_country 
HAVING COUNT(grade)>2;

Output:

CUST_COUNTRY         COUNT(GRADE)
-------------------- ------------
USA                             4
India                          10
Australia                       3
Canada                          3
UK                              5

Example 2: examples of HAVING clause in SQL

Emp_Id	Emp_Name	Emp_Salary	Emp_City
201	Abhay		2000		Goa
202	Ankit		4000		Delhi
203	Bheem		8000		Jaipur
204	 Ram		2000		Goa
205	Sumit		5000		Delhi

Show cities with total employee salaries over $5,000. In this scenario, use the HAVING clause in SQL to create the following query:

SELECT SUM(Emp_Salary), Emp_City FROM Employee GROUP BY Emp_City HAVING SUM(Emp_Salary)>5000;  

Output:

Example 3: Show the departments with a total salary of $50,000 or higher. We'll use the HAVING Clause in this case.

SELECT Department, sum(Salary) as Salary
FROM employee
GROUP BY department
HAVING SUM(Salary) >= 50000;  

Example 4: Consider the CUSTOMERS table having the following records.

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

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2;

Output:

To limit the rows returned by the SELECT operation, use the CASE expression in a HAVING clause.

Example 1: HAVING clause with CASE

USE AdventureWorks2008R2;
GO
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 
    ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M' 
        THEN ph1.Rate 
        ELSE NULL END) > 40.00
     OR MAX(CASE WHEN Gender  = 'F' 
        THEN ph1.Rate  
        ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;

Example 2: The statement delivers the maximum hourly wage for each HumanResources job title. Table for employees. The HAVING clause limits the titles to those held by males with a maximum pay rate of more than $40 or women with a maximum pay rate of more than $42.

USE AdventureWorks2008R2;
GO
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = ‘M’
THEN ph1.Rate
ELSE NULL END) > 40.00
OR MAX(CASE WHEN Gender  = ‘F’
THEN ph1.Rate
ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;

To set a condition using the select statement, utilise the HAVING clause and the SQL COUNT() function. With the SQL COUNT() method, the HAVING clause replaces the WHERE clause.

The GROUP BY with HAVING clause returns the result for a specific group of a column that matches the HAVING clause's condition. COUNT, MAX, SUM, and other aggregates are used with HAVING.

Example 1: See the following orders table from the sample database:

The HAVING clause is used in the following statement to locate customers who place at least two orders per year:

SELECT customer_id,
    YEAR (order_date),
    COUNT (order_id) order_count
FROM
    sales.orders
GROUP BY
    customer_id,
    YEAR (order_date)
HAVING
    COUNT (order_id) >= 2
ORDER BY
    customer_id;

The GROUP BY clause, for starters, groups sales orders by customer and order year. The COUNT() function returns the total number of orders placed by each customer over the course of a year.

Second, the HAVING clause filtered out any clients with fewer than two orders.

Example 2: HAVING with COUNT

Problem: List the number of customers in each country. Only include countries with more than 10 customers.

SELECT Country, COUNT(Id) AS Customers
  FROM Customer
 GROUP BY Country
HAVING COUNT(Id) > 10

Output:

COUNTRY	CUSTOMERS
France	11
Germany	11
USA	    13

Example 3: To get data of number of agents from the 'agents' table with the following condition:

Sample table : agents

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

1. number of agents must be greater than 3, the following SQL statement can be used:

SELECT COUNT( * ) 
FROM agents 
HAVING COUNT(*)>3;

Output:

The Group by and Having clauses in SQL are used to eliminate duplicate rows.

Before you can remove duplicates, you must first determine whether you have any. The columns to look for depend on the type of duplication you're looking for. Checking for duplicate main keys is a good place to start.

Example 1: Query the table and group the results by the main key, which in this case is id. The HAVING clause is then used to only return ids with a count greater than 1:

SELECT id, COUNT(*) as Count
FROM
    Orders
GROUP BY
    id
HAVING 
   COUNT(*) > 1 

Here we see that there are a few records with duplicate records, and one (11457) with triplicate records:

ID	Count
323	    2
2244	2
3467	2
11457	3

Example 2: sql having delete duplicates

select Name,Marks,grade,count(*) as cnt from stud group by Name,Marks,grade having count(*) > 1;

A DISTINCT or UNIQUE aggregate expression cannot be included in the HAVING clause.

Use a COUNT(DISTINCT Location) and join it to a Type and Color subquery. The GROUP BY and HAVING clauses, in the form you've tried to use them, will work.

Example 1: A distinct or one-of-a-kind expression cannot be included in the HAVING clause's condition. The following query, for example, fails due to a syntax issue:

SELECT order_num, COUNT(*) number, AVG (total_price) average
  FROM items
  GROUP BY order_num
  HAVING COUNT(DISTINCT *) > 2;

No error is issued, however, if the DISTINCT keyword is omitted from the example above.

Example 2: You may also use the HAVING clause to fetch records if you utilise the GROUP BY clause with DISTINCT. The HAVING clause is inserted in the following query, and we will retrieve the records whose SUM is greater than 5000.

SELECT DISTINCT(emp_name), 
emp_id, SUM(emp_sal_paid) As "Total Paid" FROM sto_emp_salary_paid
GROUP BY emp_name,emp_id
HAVING SUM(emp_sal_paid) > 5000;

Example 3: An work-around would be to have the count distinct in the select:

SELECT 
person,
count(distinct friend) as f_count
FROM people 
GROUP BY person 
HAVING f_count > 1;

Example 1:

SELECT COUNT(DISTINCT orderid) instead of DISTINCT COUNT(customerid):

USE MyCompany;
GO
SELECT COUNT(DISTINCT orderid) AS NumberOfOrdersMade, customerid AS
CustomerID
FROM tblItems_Ordered
GROUP BY customerid
HAVING COUNT(DISTINCT orderid) > 1
GO

When used outside of a COUNT, the DISTINCT will remove duplicate rows from a result set, but because you're using a GROUP BY, this won't affect your query. DISTINCT will limit the count to unique values of the column you supply to the count function when used inside COUNT. When aliasing NumberOfOrdersMade, it's better to utilise an orderid column rather than a customerid.

Example 2: count distinct metric (COUNT(DISTINCT mycolumn)), the HAVING clause generated in the Custom SQL tab of the Filter popover is invalid syntax for Impala:

SELECT category AS category,
  count(DISTINCT id) AS COUNT_DISTINCT(id)
FROM mytable
WHERE to_timestamp(cast(created_dts as string), 'yyyyMMddHH') >= '2020-05-22 00:00:00.000000'
  AND to_timestamp(cast(created_dts as string), 'yyyyMMddHH') < '2020-05-29 00:00:00.000000'
GROUP BY category
HAVING ((COUNT_DISTINCT(id) > 0))

Example 3: To check for customers who have ordered both - ProductID 2 and 3, HAVING can be used:

select customerId
 from orders
 where productID in (2,3)
 group by customerId
 having count(distinct productID) = 2

Output:

Using the GROUP BY clause, group all rows by the target column(s), the column(s) on which duplicate values should be checked.

Example 1: Checking if any of the groups have more than one entry using the COUNT function in the HAVING clause; these are the duplicate values.

SELECT username, email, COUNT(*)
FROM users
GROUP BY username, email
HAVING COUNT(*) > 1

Example 2: Using the Orders table, which is a modified version of the table we used in my earlier essay about SQL GROUP BY. Below is an example of the table.

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10248	90		5		1996-07-04	3
10249	81		6		1996-07-05	1
10250	34		4		1996-07-08	2
10251	84		3		1996-07-08	1
10251	84		3		1996-07-08	1
10252	76		4		1996-07-09	2
…	…		…		…		…
10443	66		8		1997-02-12	1

There are a few duplicates in the OrderID column in this example. Because each every order is granted its own value, each row should ideally have a unique OrderID value. This was not enabled for some reason. We can use the following query to discover duplicates:

SELECT OrderID, COUNT(OrderID)
FROM Orders
GROUP BY OrderID
HAVING COUNT(OrderID)>1

Output:

As we can see, OrderID 10251 (which we saw in the table sample above) and OrderID 10276 have duplicates.

The GROUP BY and HAVING clauses can be used to clearly display duplicates in your data. After you've verified that the rows are identical, you can use the DELETE command to eliminate the duplicate(s).

Example 1: Column 'products.UnitPrice' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

But when placing the same code under 'EXISTS' - no problems:

SELECT *
FROM products p
WHERE EXISTS (SELECT 1
  FROM products
  HAVING p.unitprice > avg(unitprice))

Example 2: A SQL query with a 'having' clause in the subquery of a 'exists' clause. That's the strange thing. There is no error, and the subquery can be used independently. The entire query, however, yields the identical results with and without the 'having' clause. This is kind of what my query looks like:

SELECT X
   FROM A
  WHERE exists (
    SELECT X, count(distinct Y)
    FROM B
    GROUP BY X
    HAVING count(distinct Y) > 2)

So I'm trying to find the rows in A that have more than two occurrences of Y in B. The results, however, include records that aren't found in the subquery.

In a query, the HAVING clause must come after the GROUP BY clause and, if used, before the ORDER BY clause.

The purpose is determined by the WHERE clause. It is used to apply conditions to columns in order to identify which columns are part of the group's final result set. With the WHERE clause, we don't have to employ coupled functions like COUNT (), SUM (), and so on. Following that, we must employ a HAVING clause.

A query is created using the SQL WHERE clause and the HAVING clause. The ORDER BY clause sets the final result in a certain order. Ascending is the default order.

sql having groupby and orderby clause Syntax:

SELECT column1, function_name(column2)  
FROM table_name  
WHERE condition  
GROUP BY column1, column2  
HAVING condition  
ORDER BY column1, column2;  

Example 1: To get list of cust_city, sum of opening_amt, average of receive_amt and maximum payment_amt from customer table with following conditions:

1. grade of customer table must be 2, average of receive_amt for each group of cust_city must be more than 500, the output should be arranged in the ascending order of SUM(opening_amt), then, the following SQL statement can be used:

SELECT cust_city,SUM(opening_amt), 
AVG(receive_amt),MAX(payment_amt) 
FROM customer 
WHERE grade=2 
GROUP BY cust_city 
HAVING AVG(receive_amt)>500 
ORDER BY SUM(opening_amt); 

Example 2: The aggregate function in the HAVING clause is used to locate albums with a number of songs between 18 and 20, as illustrated in the following sentence:

SELECT albumid,
   COUNT(trackid)
FROM
   tracks
GROUP BY
   albumid
HAVING 
   COUNT(albumid) BETWEEN 18 AND 20
ORDER BY albumid;

Example 3:

SELECT NAME, SUM(SALARY) FROM Employee   
GROUP BY NAME  
HAVING SUM(SALARY)>23000
ORDER BY SALARY;

Output:

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

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
GROUP BY age
HAVING COUNT(age) >= 2
ORDER BY NAME;

Output:

GROUP BY and HAVING. After the GROUP BY, the HAVING clause is checked. A HAVING clause can be used to test the aggregated values.

Example 1: The query only picks records with the productIDs in question, and the HAVING clause looks for groups with two productIds rather than simply one.

select customerId
 from orders
 group by customerId
 having sum(case when productID = 2 then 1 else 0 end) > 0
    and sum(case when productID = 3 then 1 else 0 end) > 0

This query selects only groups having at least one record with productID 2 and at least one with productID 3.

Example 2: Only those items with a total purchase quantity more than 1500 will be displayed.

The following statement only returns items with a total purchase quantity greater than 1500.

SELECT ITEM , SUM(ITEM_QTY) AS TOTAL_QTY 
FROM dbo.ITEM_DETAILS 
GROUP BY Item
HAVING SUM(ITEM_QTY) > 1500

As you can see, it returns two items for which total purchased quantity is greater than 1500.

Example 3: SQL Having Clause Sum Example

Use a single condition in the SQL Server Having sum.

SELECT [Occupation]
      ,Education
   ,SUM([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  GROUP BY [Occupation], [Education]
  HAVING SUM([YearlyIncome]) > 60000

First, Group By will categorize the customers by Occupation and Education. Next, Having Clause will check the condition whether the SUM([Yearly Income]) > 60000.

Example 4: Show the total population of those continents with a total population of at least half a billion.

SELECT continent, SUM(population)
  FROM world
 GROUP BY continent
HAVING SUM(population)>500000000

Example 1: To find if any of the employees has 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;

Example 2: If you want to see the names of customers instead of numbers, you can join the payments table with the customers table:

SELECT customerName, 
    MAX(amount)
FROM
    payments
INNER JOIN customers USING (customerNumber)    
GROUP BY 
    customerNumber
HAVING 
    MAX(amount) > 80000
ORDER BY 
    MAX(amount);

Example 3: The following statement queries data from tracks and albums tables using inner join to find albums that have the total length greater than 60,000,000 milliseconds.

SELECT tracks.AlbumId,
	title,
	SUM(Milliseconds) AS length
FROM
	tracks
INNER JOIN albums ON albums.AlbumId = tracks.AlbumId
GROUP BY
	tracks.AlbumId 
HAVING
	length > 60000000;

You may obtain the maximum value for each group using the MAX() function and the GROUP BY clause. You can use the MAX() function in a HAVING clause to filter categories based on a criteria.

Example 1: To return the name of each department and the department's maximum salary using the SQL MAX function. Only departments with a maximum salary of less than $50,000 will be returned by the SQL HAVING clause.

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;

Example 2: Let’s run the following query that selects only the highest salary paid to each employee and the salaries are greater than 28000.

SELECT name, max(salary_$) FROM emp_info
GROUP BY age, name
HAVING max(salary_$) > 28000;

Rony, Lillian, and Dan all have wages greater than 28000 in the above output.

Let's try the max function with another table. We have a table called major_cities that includes the names of cities in the United Kingdom as well as their populations.

Example 3: By utilising the SQL MAX() aggregate function within the HAVING clause, limit the query result set to only those employees with a very big salary difference from the maximum salary values, as seen below:

SELECT TOP 10 ID, EmpName, EmpDateOfBirth, 
    YoungEmp = (SELECT MAX(EmpDateOfBirth) FROM MAXDemo), EmpSalary, 
     LargeSalary = (SELECT MAX(EmpSalary) FROM MAXDemo )
FROM MAXDemo
WHERE EmpIsActive = 1
GROUP BY ID, EmpName, EmpDateOfBirth , EmpSalary
HAVING ((SELECT MAX(EmpSalary) FROM MAXDemo)- EmpSalary) > 100
 ORDER BY EmpSalary DESC

Example 4: The possible values for DayofMon are "15" and "30" (I have data coming to this table on every two days of month. eg:- 14 th and 30th, or 15th and 31st )

SELECT [OrganisationID] 
    ,[MailboxLocationName] AS Location 
    ,MailboxCode as LocationCode 
    ,DayofMon 
    ,COUNT(DISTINCT [UPN]) AS [Count]
FROM 
    [ExchangeUserMailbox]
GROUP BY 
    [OrganisationID], [MailboxLocationName], DayofMon, MailboxCode
HAVING  
    DayofMon = MAX(DayofMon) 

Above query to get all records with the maximum value of DayofMon.

You may obtain the maximum value for each group by using the MAX() function and the GROUP BY clause.

You can use the MAX() function in a HAVING clause to filter groups based on a criteria.

Example 1: The following query discovers each customer's largest payment and returns only payments with sums larger than 80,000 based on the returned payments.

SELECT customerNumber, 
MAX(amount)
FROM
    payments
GROUP BY customerNumber
HAVING MAX(amount) > 80000
ORDER BY MAX(amount);

Example 2: To get data of 'agent_code', and number of agents for each group of 'agent_code' from the orders table with the following conditions:

The outcome of an outer query [SELECT MAX(agent_code).......] will be 'agent_code' for a group. under the following circumstances [SELECT agent_code, COUNT(agent_code) mycount FROM orders GROUP BY agent_code] The outer query returns the maximum number of agents mentioned as'mycount'. The inner query produced the data 'agent_code' number of agents from the 'orders' table as column alias 'mycount' with the following criteria :

SELECT agent_code, COUNT(agent_code) 
FROM orders  GROUP BY agent_code 
HAVING COUNT (agent_code)=( 
SELECT MAX(mycount) 
FROM ( 
SELECT agent_code, COUNT(agent_code) mycount 
FROM orders 
GROUP BY agent_code));

The HAVING Clause is always combined with the GROUP BY Clause. The HAVING Clause restricts data to group records instead of particular entries.

Example 1: Multiple Conditions in SQL Multiple Conditions are used in the Having Clause in this SQL Server examples. The Group By statement groups meetingIDs by caseID.

GROUP BY meetingID
HAVING COUNT(caseID) < 4 AND COUNT(caseID) > 2

Example 2: The query only picks records with the productIDs in question, and the HAVING clause looks for groups with two productIds rather than simply one.

select customerId
 from orders
 group by customerId
 having sum(case when productID = 2 then 1 else 0 end) > 0
    and sum(case when productID = 3 then 1 else 0 end) > 0

This query selects only groups having at least one record with productID 2 and at least one with productID 3.

Example 3: This example uses Multiple Conditions in it. First, query will organize the customers by Occupation & Education. Next, it will check the condition whether the SUM([Yearly Income]) > 60000 and less than 200000.

SELECT [Occupation]
      ,Education
   ,SUM([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  GROUP BY [Occupation], [Education]
  HAVING SUM([YearlyIncome]) > 60000 AND
 SUM([YearlyIncome]) < 200000

By comparing a field in the main table with the results of the subquery, a subquery in the HAVING clause aids in filtering out groups of records.

The HAVING clause filters groups in the same way that the WHERE clause filters rows of records. When a subquery occurs in the HAVING clause, it is used as part of the row group selection, just like any other expression in the HAVING clause.

It's particularly handy for filtering aggregate quantities like averages, summations, and counts.

You no longer have to hard-code values within the comparisons when you utilise a subquery in the HAVING clause. You may rely on the results of the subquery to do this for you.

Example 1: Find the departments where the total number of employees is more than the total number of employees in Theni.

SELECT d.departmentname,count(e.employeeid)
FROM department as d INNER JOIN employees as e
ON d.departmentid::varchar = e.departmentid
GROUP BY d.departmentname
HAVING count(e.employeeid)>(SELECT count(employeeid) FROM employees WHERE city = 'Theni');

Example 2: It is now able to match a group's average to the total average, for example. We could always utilise the group average in the HAVING clause, but there was no way to compute the overall average. This is now possible thanks to subqueries.

In this case, we're looking for employee job titles with more vacation hours left than the average for all employees.

SELECT JobTitle,
    AVG(VacationHours) AS AverageVacationHours
FROM HumanResources.Employee
GROUP BY JobTitle
HAVING AVG(VacationHours) > 50

Example 3: Here is a request that lends itself naturally to a query with a subquery in the HAVING clause: "Which products' average in-stock quantity is more than double the average number of each item ordered per customer?"

SELECT Name, AVG( Quantity )
FROM Products
GROUP BY Name
HAVING AVG( Quantity ) > 2* (
   SELECT AVG( Quantity )
   FROM SalesOrderItems 
 );

Output:

name		AVG( Products.Quantity )
Baseball Cap	62.000000
Shorts		80.000000
Tee Shirt	52.333333

The query executes as follows:

The average quantity of items in the SalesOrderItems table is calculated by the subquery.

After that, the primary query walks over the Products table, computing the average quantity per product and grouping by product name.

The HAVING clause then examines if each average quantity is greater than the subquery's result. If this is the case, the main query will return that row group; otherwise, it will not.

The SELECT clause generates a single summary row for each group, indicating the name of each product as well as the average quantity in stock.

This is why aliases are not permitted in the HAVING clause. Alternatively, department id could be utilised with the HAVING clause. In other words, any input in the SELECT statement will be accepted by the HAVING clause. The HAVING clause is used to limit the GROUP BY results.

The ORDER BY clause is the only location in MS SQL where aliases can be referenced (that I'm aware of). Many other database platforms have the ability to reference aliases in other sections of the query, and it irritates me that Microsoft hasn't thought it's a useful enough feature to provide.

Example 1: When we have to combine tables, we frequently find that they have columns with the same names. When we refer to such a column in a query, we're not sure which table the reference is for. This ambiguity is frequently alleviated by prefixing column names with table names. When we link a table to it, however, even the table name becomes ambiguous, necessitating the usage of aliases to authorise table references.

SELECT lbr.booknumber, odr.Status, odr.shippedDate,     
SUM(bookissue) AS NumberOfIssuedBooks,  
SUM(costeach*bookissue) AS TotalCost  
FROM library lbr  
INNER JOIN bookorder odr  
ON lbr.booknumber = odr.booknumber  
GROUP BY odr.booknumber  
HAVING odr.status = 'Done' AND TotalCost > 5000;

Main Article :- Sql difference between HAVING and WHERE

The major distinction is that the WHERE clause is used to filter records before any groupings are established, whereas the HAVING clause is used to filter values from a group. We shall first learn these SQL clauses before comparing them.

Example with difference:

1. In the logical order of query processing, the WHERE clause comes after the FROM clause, which implies it comes before the GROUP BY clause, whereas the HAVING clause comes after groups are established.

2. If you use the HAVING clause without the group by clause, it can refer to any column, but unlike the WHERE clause, the index will not be applied. The next examples have the same result set, but "where" will utilise the id index and "having" will perform a table scan.

select * from table where id = 1
select * from the table having id = 1

3. With the HAVING clause, you may apply an aggregate function to filter rows. You can use an aggregate function in the logical expression since the HAVING clause is executed after the rows have been grouped. The following query, for instance, will only show classes with more than 10 students :

SELECT Course, COUNT(Course) as NumOfStudent 
FROM Training 
GROUP BY Course 
HAVING COUNT(Course)> 10

4. Another significant distinction between the WHERE and HAVING clauses is that WHERE uses an index while HAVING does not; for example, the following two queries yield similar results, but WHERE uses an index while HAVING uses a table scan

SELECT * FROM Course WHERE Id = 101;
SELECT * FROM Course HAVING Id = 102;

5. The WHERE clause evaluates for per row since it is assessed before groups are established. The HAVING clause, on the other hand, is assessed after groups are generated, therefore it evaluates per group.

Example:

This sample compares the Where clause and the Having clause in SQL Server. The following query will first extract data from customers whose education level is less than [Partial High School].

The Collective Customers are grouped by occupation and education using the by statement. Finally, the Having Clause will determine whether or not SUM([Yearly Income]) > 60000.

SELECT [Occupation]
  ,Education
   ,SUM([YearlyIncome]) AS [Sum Of Income]
   ,MAX([YearlyIncome]) AS [Max Income]
  FROM [Customer] 
  WHERE Education <> 'Partial High School'
  GROUP BY [Occupation], [Education]
  HAVING SUM([YearlyIncome]) > 60000

GROUP BY is not required to employ HAVING caluse. With the GROUP BY clause, the SQL HAVING clause is frequently utilised.

The where and having clauses cannot act independently of each other because there is no grouping between them. Because it impacts rows in a single group rather than groups, having functions similarly to where, except the having clause can still employ aggregates.

You can also use the having clause in conjunction with the SQL extension to remove the group by clause from a query that includes an aggregate in its select list. These scalar aggregate functions calculate values for the entire table, not for individual groups.

Because HAVING is used to filter the data after it has been grouped according to a specific column in the table.

In Standard SQL, a HAVING clause without a GROUP BY clause is legal and (perhaps) beneficial syntax. Because aggregate functions work on the table expression as a set all at once, it only makes sense to utilize them.

Example 1: HAVING without GROUP BY

SELECT AVG(Sales) AS Avg_Sales
FROM ORDERS
HAVING AVG(Sales) > 500

Example 2: They don't have to coexist, as proved by the fact that the following query in Oracle works:

select * from dual having 1 = 1;

Similarly, in PostgreSQL the following query works:

select 1 having 1 = 1;

So having doesn't require group by.

Example 3: Standard SQL allows you to utilise a HAVING clause without a GROUP BY clause. Only aggregate functions make sense because they operate on the table expression all at once as a set. In your example:

Book HAVING NumberOfPages = MAX(NumberOfPages)

isn't correct because NumberOfPages refers to which row in the entire table. Similarly, using literal values in the SELECT clause is the only way to go.

Consider this example, which is valid Standard SQL:

SELECT 'T' AS result
   FROM Book
HAVING MIN(NumberOfPages) < MAX(NumberOfPages);

The query will never return more than one record, even if the DISTINCT keyword is not used. If the HAVING clause is true, the result will be a single row with a single column with the value 'T' (showing that we have books with different page counts), otherwise the result will be the empty set (zero rows with a single column).

I believe the query does not fail in mySQL because of proprietary extensions that cause the HAVING clause to appear (logically) after the SELECT clause (the standard approach is the other way around), in combination with the implicit GROUP BY clause.

Example 4: The having clause is used in the following example: It takes the average price, removes titles with advances of more than $4,000 from the results, and generates results where the price is less than the average price:

select title_id, advance, price
from titles
where advance < 4000
having price > avg(price)

Output:

title_id        advance    price
-------------  ---------  --------
BU1032          5,000.00     19.99
BU7832          5,000.00     19.99
MC2222              0.00     19.99
PC1035          7,000.00     22.95
PC8888          8,000.00     20.00
PS1372          7,000.00     21.59
PS3333          2,000.00     19.99
TC3218          7,000.00     20.95