x

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
more ▼

asked Nov 27 '09 at 11:36 AM in Default

geee gravatar image

geee
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Nov 27 '09 at 01:34 PM

Jason Cumberland gravatar image

Jason Cumberland
507 2

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 28 '09 at 05:32 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1936
x362
x34

asked: Nov 27 '09 at 11:36 AM

Seen: 3104 times

Last Updated: Nov 28 '09 at 05:21 AM