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

  • Learn T-SQL Querying
  • Pedro Lopes Pam Lahoud
  • 190字
  • 2021-06-24 14:38:10

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.
主站蜘蛛池模板: 扶余县| 舟山市| 成都市| 千阳县| 仁寿县| 青冈县| 通江县| 株洲市| 西宁市| 沙湾县| 吴桥县| 铜鼓县| 南宫市| 勃利县| 北安市| 行唐县| 昆明市| 伊金霍洛旗| 河曲县| 公安县| 滁州市| 雷州市| 周宁县| 武鸣县| 昌江| 新巴尔虎左旗| 荆州市| 子长县| 海宁市| 卓资县| 武鸣县| 临湘市| 台东市| 赤城县| 克山县| 禄劝| 陇川县| 漳平市| 神农架林区| 塔城市| 灌南县|