Sql – How to Use Group By clause when we use Aggregate function in the Joins

aggregate-functionsgroup-bysqlsql-serversql-server-2005

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.

Best Solution

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.