当我们在联接中使用聚合函数时如何使用Group By子句？

I want to join three tables and to calculate the Sum(Quantity) of the Table A. I tried something and I get the desired output. But still I have confusion based on aggregate function and Group By clause.

我想加入三个表并计算表A的总和（数量）。我尝试了一些东西，然后得到了所需的输出。但我仍然混淆基于聚合函数和Group By子句。

While calculating the sum value by joining two or more tables, what are the columns we need to mention in the Group By clause and why do we need to give those columns?

在通过连接两个或多个表来计算总和值时，我们需要在Group By子句中提及哪些列，为什么我们需要提供这些列？

For Example: Here is my table and the desired query.

例如：这是我的表和所需的查询。

TableA: ItemID, JobOrderID, CustomerID, DivisionID, Quantity
TableB: ItemID, ItemName, SpecificationID
TableC: SpecificationID, SpecificationName
TableD: DivisionID, DivisionName
TableE: JobOrderID, JobOrderNo.
TableF: CustomerID, CustomerName

I want to get the Sum(Quantity) based on ItemID, CustomerID, JobOrderID and DivisionID.

我想根据ItemID，CustomerID，JobOrderID和DivisionID获得Sum（Quantity）。

I wrote the following query and it's working fine. But if I remove any column in the Group By clause, it doesn't give the desired result. Why? What does the Group By clause do here? How to specify the Group By clause when using Aggregate function? Here is my Query.

我写了以下查询，它工作正常。但是，如果我删除Group By子句中的任何列，则它不会提供所需的结果。为什么？ Group By子句在这里做什么？如何在使用Aggregate函数时指定Group By子句？这是我的查询。

    SELECT 
            B.ItemName + ' - ' + C.SpecificationName AS 'ItemName',
            SUM(A.Quantity) AS 'Quantity',
            A.ItemID,
            D.DivisionName,
            F.CustomerName,
            E.JobOrderNo,
            A.DivisionID,
            A.JobOrderID,
            A.CustomerID

    FROM
            TableA A  
            INNER JOIN TableB B ON B.ItemID = A.ItemID 
            INNER JOIN TableC C ON C.SpecificationID = B.SpecificationID
            INNER JOIN TableD D ON D.DivisionID = A.DivisionID
            LEFT JOIN TableE E ON E.JobOrderID = A.JobOrderID
            LEFT JOIN TableF F ON F.CustomerID = A.CustomerID
    WHERE
            A.ItemID = @ItemID
    GROUP BY
            A.ItemID,
            A.JobOrderID,
            A.DivisionID,
            A.CustomerID,
            D.DivisionName,
            F.CustomerName,
            E.JobOrderNo,
            B.ItemName,
            C.SpecificationName

Any one please give suggestion about the Group By Clause by considering this as an example.

任何人请以此为例，对Group By Clause提出建议。

3 个解决方案

#1

GROUP BY for any unique combination of the specified columns does aggregation (like sum, min etc). If you don't specify some column name in the GROUP BY clause or in the aggregate function its unknown to the SQL engine which value it should return for that kind of column.

对于任何指定列的唯一组合，GROUP BY都会进行聚合（如sum，min等）。如果未在GROUP BY子句或聚合函数中指定某些列名，则SQL引擎不知道它应为该类列返回哪个值。

#2

GROUP BY (Transact-SQL) groups a selected set of rows into a set of summary rows by the values of one or more columns or expressions in SQL Server 2008 R2. One row is returned for each group. Aggregate functions in the SELECT clause list provide information about each group instead of individual rows.

GROUP BY（Transact-SQL）通过SQL Server 2008 R2中的一个或多个列或表达式的值将选定的一组行分组为一组摘要行。每组返回一行。 SELECT子句列表中的聚合函数提供有关每个组而不是单个行的信息。

SELECT a.City, COUNT(bea.AddressID) AS EmployeeCount
FROM Person.BusinessEntityAddress AS bea 
    INNER JOIN Person.Address AS a
        ON bea.AddressID = a.AddressID
GROUP BY a.City

The GROUP BY clause has an ISO-compliant syntax and a non-ISO-compliant syntax. Only one syntax style can be used in a single SELECT statement. Use the ISO compliant syntax for all new work. The non-ISO compliant syntax is provided for backward compatibility.

GROUP BY子句具有符合ISO的语法和不符合ISO的语法。在单个SELECT语句中只能使用一种语法样式。对所有新工作使用ISO兼容语法。提供非ISO兼容语法是为了向后兼容。

In ISO-compliant syntax each table or view column in any nonaggregate expression in the list must be included in the GROUP BY list.

在符合ISO的语法中，列表中任何非聚合表达式中的每个表或视图列都必须包含在GROUP BY列表中。

select pub_id, type, avg(price), sum(total_sales)
from titles
group by pub_id, type

Refering to Organizing query results into groups: the group by clause

请参阅将查询结果组织到组中：group by子句

Sybase or non-ISO-compliant syntax lifts restrictions on what you can include or omit in the select list of a query that includes group by:

Sybase或非ISO兼容语法解除了对包含group by的查询的选择列表中可包含或省略的内容的限制：

• The columns in the select list are not limited to the grouping columns and columns used with the vector aggregates.

  选择列表中的列不限于与向量聚合一起使用的分组列和列。

• The columns specified by group by are not limited to those non-aggregate columns in the select list.

  group by指定的列不限于选择列表中的那些非聚合列。

Example:

例：

select type, title_id, avg(price), avg(advance) 
from titles 
group by type 

#3

To use aggregate functions like sum without group by, use the over clause.

要使用sum而不使用group by这样的聚合函数，请使用over子句。

See: http://msdn.microsoft.com/en-us/library/ms189461.aspx

请参阅：http：//msdn.microsoft.com/en-us/library/ms189461.aspx

Example:

例：

CREATE TABLE #a (ida int, name varchar(50))
CREATE TABLE #b  (ida int, number int)

INSERT INTO #a VALUES(1,'one')
INSERT INTO #a VALUES(2,'two')

INSERT INTO #b VALUES(1,2)
INSERT INTO #b VALUES(1,3)
INSERT INTO #b VALUES(2,1)

SELECT DISTINCT a.ida, sum(number) OVER (PARTITION BY a.ida) FROM #a a
INNER JOIN #b b on a.ida = b.ida