M. Use HAVING with SUM and AVG

This query uses the

clause in the

clause.

The following example shows using

,

,

, and

clauses in one

statement. It produces groups and summary values but does so after eliminating the

products with prices over $25 and average order quantities under 5. It also organizes the

results by.

The following example groups the

table by product ID and includes only

those groups of products that have orders totaling more than

and whose average

order quantities are less than.

LIKE

HAVING

GROUP BY

HAVING

WHERE

ORDER BY

SELECT

ProductID

SalesOrderDetail

$1000000.00

3

ORDER
BY
ProductID;
GO
USE
AdventureWorks2025;
GO
SELECT
SalesOrderID,
CarrierTrackingNumber
FROM
Sales.SalesOrderDetail
GROUP
BY
SalesOrderID, CarrierTrackingNumber
HAVING
CarrierTrackingNumber
LIKE
'4BD%'
ORDER
BY
SalesOrderID;

USE
AdventureWorks2025;
GO
SELECT
ProductID
FROM
Sales.SalesOrderDetail
WHERE
UnitPrice < 25.00
GROUP
BY
ProductID
HAVING
AVG (OrderQty) > 5
ORDER
BY
ProductID;
GO