I am having a problem with a group by: Column 'dbo.Accounts.AccountDateOpen' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
If I add 'dbo.Accounts.AccountDateOpen' to the Group BY , it doesnt group by!
You need to include the case statement (minus the alias) in the group by clause.
answered Nov 27, 2009 at 01:34 PM
As qcumberland says re: the GROUP clause needing to contain all the displayed columns [which are not part of an aggregate function). Personally I prefer to do this using a nested query so that the logic is not repeated twice (which can lead to errors if only one "copy" is edited in the future)
SELECT TOP 100 PERCENT g.GROF, Grof, COUNT_BIG(*)OpenedAccounts, YearWeek, YearQuarter FROM ( SELECT g.Grof, (YEAR(a.AccountDateOpen)*100)+ DATEPART(week,a.AccountDateOpen)YearWeek, CASE WHEN DATEPART(week,a.AccountDateOpen) = 27 THEN 200903 ELSE ((YEAR(a.AccountDateOpen)*100)+ DATEPART(quarter,a.AccountDateOpen)) END YearQuarter FROM dbo.Grof AS g INNER JOIN dbo.Accounts a ON a.FKtoCustomerGrof = g.Grof ) AS X GROUP BY Grof,YearWeek, YearQuarter ORDER BY Grof, YearQuarter
I also recommend you use the column names in the ORDER BY, rather than ordinal column positions; again, it reduces bugs when the code is changed. (You can use the Alias name).
SELECT TOP 100 PERCENT is going to select everything, and is thus redundant (although this workaround was used in VIEWS to enable them to have a Sort Order I believe that this trick has been deprecated (or just plain "doesn't work", I've forgotten which) in later versions of SQL Server.
answered Nov 28, 2009 at 05:32 AM