官术网_书友最值得收藏!

HAVING

HAVING further filters the result based on values in the results, rather than the actual data. A HAVING clause only applies to columns that are included in the GROUP BY clause or in an aggregate function. Building on the same example used in the WHERE, ORDER BY, and GROUP BY sections, here we want to additionally know which locations carry an inventory of over 100 items per product. For that, after the GROUP BY clause, the query has a HAVING clause over the aggregate function, where its result is greater than 100:

SELECT P.Name AS ProductName, SUM([PI].Quantity) AS Total_Quantity, L.Name AS LocationName
FROM Production.Product AS P
INNER JOIN Production.ProductInventory AS [PI] ON P.ProductID = [PI].ProductID
INNER JOIN Production.Location AS L ON [PI].LocationID = L.LocationID
WHERE P.Name LIKE 'Touring%'
GROUP BY P.Name, L.Name
HAVING SUM([PI].Quantity) > 100
ORDER BY P.Name DESC, L.Name DESC;

The following screenshot shows the results as containing only rows with an aggregate Total_Quantity greater than 100:

主站蜘蛛池模板: 宜州市| 鹰潭市| 安吉县| 宁安市| 上饶县| 五台县| 铜山县| 女性| 祁连县| 攀枝花市| 钟祥市| 横峰县| 静安区| 新乡县| 七台河市| 三都| 渭源县| 南宁市| 富裕县| 钦州市| 扎兰屯市| 贵阳市| 西城区| 界首市| 凌源市| 广水市| 德昌县| 聂荣县| 读书| 白城市| 合山市| 定安县| 类乌齐县| 阳新县| 民乐县| 河津市| 泌阳县| 钟祥市| 巩义市| 福安市| 厦门市|