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.
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Using Group By with Inner JoinSQL Inner Join permits us to use Group by clause along with aggregate functions to group the result set by one or more columns. Group by works conventionally with Inner Join on the final result returned after joining two or more tables.
The Group By statement is used to group together any rows of a column with the same value stored in them, based on a function specified in the statement. Generally, these functions are one of the aggregate functions such as MAX() and SUM(). This statement is used with the SELECT command in SQL.
If you don't specify GROUP BY , aggregate functions operate over all the records selected. In that case, it doesn't make sense to also select a specific column like EmployeeID .
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 (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.
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.
In ISO-compliant syntax each table or view column in any nonaggregate expression in the list must be included in the GROUP BY
list.
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
Sybase or non-ISO-compliant syntax lifts restrictions on what you can include or omit in the
select
list of a query that includesgroup 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.
Example:
select type, title_id, avg(price), avg(advance)
from titles
group by type
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With