Group BY on Case
HI All
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!
SELECT TOP 100 PERCENT g.GROF,
COUNT_BIG(*)OpenedAccounts,
(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
GROUP BY g.Grof,((YEAR(a.AccountDateOpen)*100)+ DATEPART(week,a.AccountDateOpen))
ORDER BY 1,3
You need to include the case statement (minus the alias) in the group by clause.
SELECT TOP 100 PERCENT
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
,COUNT_BIG(*) OpenedAccounts
FROM
dbo.Grof AS g
INNER JOIN
dbo.Accounts a
ON
a.FKtoCustomerGrof = g.Grof
GROUP BY
g.GROF
,(YEAR(a.AccountDateOpen)*100)+ DATEPART(week,a.AccountDateOpen)
,CASE
WHEN DATEPART(week,a.AccountDateOpen) = 27 THEN 200903
ELSE ((YEAR(a.AccountDateOpen)*100)+ DATEPART(quarter,a.AccountDateOpen))
END
ORDER BY
1,3
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.
