March 29, 2016 at 9:05 am
Neither of those statements are true.
Using AdventureWorks
Valid:
SELECT CustomerID, SUM(TotalDue) FROM Sales.SalesOrderHeader
GROUP BY CustomerID, SalesOrderNumber, TerritoryID
Valid:
SELECT CustomerID, LEFT(SalesOrderNumber,5), SUM(TotalDue) FROM Sales.SalesOrderHeader
GROUP BY CustomerID, SalesOrderNumber, TerritoryID
Any column referenced in the SELECT has to either be in an aggregate or in the group by. The expression I put in the select is valid because it's deterministic and the column it operates on is in the Group By clause.
It's generally recommended that any columns in the group by also be in the select, as the results can be hard to understand if they're not, but it's not a requirement
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2016 at 9:53 am
When you say
"any column referenced in the SELECT has to either be in an aggregate or in the group by"
. What does aggregate mean in this context? I know about the typical aggregates ie LIKE SUM AVG. Also I thought GROUP BY was in the general category of aggregates. I know that we are splitting hairs but I am little shaky on this
March 29, 2016 at 9:56 am
Aggregate function.
https://msdn.microsoft.com/en-us/library/ms173454.aspx
GROUP BY isn't an aggregate, it's a clause in the statement that defines what columns the aggregates are computed by.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2016 at 9:58 am
got it. thanks : )
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply