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

GROUP BY

GROUP BY aggregates the results on the required column names or expressions. Building on the same example from the ORDER BY section, we want to know the overall product quantity per product name and location, from the following code snippet. The Quantity column is using the aggregate function SUM. Therefore, the remaining columns need to be contained in the aggregation GROUP BY clause:

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
ORDER BY P.Name DESC, L.Name DESC;

The following screenshot shows the results with one row per set as defined by the GROUP BY clause:

Aggregations can be further specified by using the following keywords:

  • ROLLUP: Specifies the creation of subtotals and totals for the required column names or expressions.
  • CUBE: Specifies the creation of subtotals and totals for all combinations of columns in the GROUP BY clause.
  • GROUPING SETS: Allows the use of multiple GROUP BY clauses, such as using ROLLUP and CUBE together.
主站蜘蛛池模板: 普洱| 嘉黎县| 东至县| 巢湖市| 五华县| 延庆县| 于田县| 湖南省| 叙永县| 华池县| 眉山市| 五原县| 洪雅县| 玉田县| 腾冲县| 大丰市| 边坝县| 海原县| 彭泽县| 凤冈县| 贵南县| 抚州市| 英吉沙县| 株洲市| 云安县| 寻甸| 泾阳县| 土默特右旗| 济南市| 东山县| 三河市| 石台县| 永昌县| 英吉沙县| 咸宁市| 永清县| 万盛区| 峨眉山市| 关岭| 蚌埠市| 达日县|