Steven Willis (3/5/2013)
calvo (3/5/2013)
Steven Willis (3/5/2013)
Another old habit to break if you are using SQL2008 or greater is to avoid GROUP BY when all you really need is an aggregated column value....
You can still use GROUP BY of course if you need to group the portfolios themselves but just to do sums or counts (or min or max, etc) the OVER() clause makes things a lot easier.
Out of curiosity, why avoid GROUP BY? Do you suggest this only because it is "easier"?
Well, yes, to a degree I find it easier to maintain and more readable if it will do the job.
But sometimes you may want to SUM on one grouping and COUNT on a different grouping. You can do that using the OVER() clause but it would get complicated when the only grouping tool available is GROUP BY.
How many times have you received the error message: "Column 'dbo.TableName.ColName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." :crazy:
The only time I've used an OVER() clause is with ROW_NUMBER. I can't wait to give it a shot with aggregates, I was totally unaware that it could be used with them.
Hey, learned something new!