March 29, 2016 at 8:59 am
Here is something that I read and I cant figure it out. (by the way this is my last post for awhile).
Every column listed in GROUP BY must be a retrieved column or a valid expression (but not an aggregate function). If an expression is used in the SELECT statement, that same expression must be specified in GROUP BY.
My questions:
1) I don't understand why every column in a GROUP BY must be a retrieved column. Why is this? I take it that the would we error out if not listed in a retrieved column.
2) I don't understand if an expression in the SELECT statement, that same expression must be specified in the GROUP BY.
I feel that I a missing some critical logic with regards to how the GROUP BY wants to work.
By the way this will be my last newbie question for awhile.
Thanks again everyone for your patience and insights...They make a huge impact when learning this code
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply