Group BY on Case

Question

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

Answer 1

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

Answer 2

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.

Group BY on Case

Follow this question

Related Questions