在Joins中使用Aggregate函数时如何使用Group By子句？ [英] How to Use Group By clause when we use Aggregate function in the Joins?

问题描述

我想加入三张表并计算表A的总和（数量）。
我尝试了一些内容并获得了所需的输出。但是我仍然基于聚合函数和Group By子句产生混淆。

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

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

$表B：ItemID，JobOrderID，CustomerID，DivisionID，数量
TableB：ItemID，ItemName，SpecificationID
TableC：SpecificationID，SpecificationName
TableD：DivisionID，DivisionName
TableE：JobOrderID，JobOrderNo。
TableF：CustomerID，CustomerName

我想根据 ItemID ， CustomerID ， JobOrderID 和 DivisionID 。

我写了下面的查询，它工作正常。但是，如果我删除Group By子句中的任何列，它不会给出所需的结果。为什么？ Group By子句在这里做什么？如何在使用聚合函数时指定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 
  

任何一个人都应该对Group By Clause提出建议，以此为例。

解决方案

GROUP BY 对于指定列的任何唯一组合都会执行聚合（如sum，min等）。如果您不在 GROUP BY 子句中指定某个列名称，或者在聚合函数中未指定某个列名称，那么它应该为该类型列返回值。 / p>

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.

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?

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.

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.

    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 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.

这篇关于在Joins中使用Aggregate函数时如何使用Group By子句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！