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