Problem Using Calculated Columns in GROUP BY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem Using Calculated Columns in GROUP BY

Author

Topic

AspNetGuy
Starting Member

5 Posts

Posted - 2010-07-12 : 11:54:30

I am using two calculated columns in a SELECT statement's GROUP BY clause and SSMS is giving an error messaged (as seen below).

Any help would be greatly appreciated.

CREATE TABLE Groups.JournalEntry
(
JournalEntryID int IDENTITY(1,1) NOT NULL,

DateCreated datetime NOT NULL,
CreatedBy uniqueidentifier NOT NULL,
DateUpdated datetime NOT NULL,
UpdatedBy uniqueidentifier NOT NULL,
RowTimeStamp timestamp,

MemberID int NOT NULL,
JournalEntrySubject nvarchar(128)NOT NULL,
DateEntered datetime NOT NULL,
Content nvarchar(max) NOT NULL,
ContentPrivate nchar(1)
CONSTRAINT CK_JournalEntry_ContentPrivate
CHECK ( ContentPrivate IN('Y','N')),
ViewCount int NOT NULL,
rowguid uniqueidentifier ROWGUIDCOL NOT NULL,
CONSTRAINT PK_JournalEntry_JournalEntryID PRIMARY KEY CLUSTERED
(
JournalEntryID ASC
)
)

SELECT
MemberID,
JournalEntryID,
DateEntered,
JournalEntryYear = (YEAR(DateEntered)),
JournalEntyMonth = (MONTH(DateEntered))
FROM Groups.JournalEntry
WHERE @MemberID = MemberID
GROUP BY JournalEntryYear, JournalEntyMonth
ORDER BY JournalEntryYear DESC, JournalEntyMonth DESC

Msg 207, Level 16, State 1, Procedure GetJournalEntryDatesByMemberID, Line 18
Invalid column name 'JournalEntryYear'.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-12 : 11:57:20

GROUP BY
YEAR(DateEntered) ,
MONTH(DateEntered)

Jim

Everyday I learn something that somebody else already knew

AspNetGuy
Starting Member

5 Posts

Posted - 2010-07-12 : 13:31:57

Thanks Jim. It worked like a champ. However, I did have to remove he other non-aggregate columns.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-12 : 14:17:04

Sorry, my reply ws way too short

SELECT
MemberID,
JournalEntryID,
DateEntered,
JournalEntryYear = (YEAR(DateEntered)),
JournalEntyMonth = (MONTH(DateEntered))
FROM Groups.JournalEntry
WHERE @MemberID = MemberID
GROUP BY
MemberID,
JournalEntryID,
DateEntered,
YEAR(DateEntered),
MONTH(DateEntered))
ORDER BY JournalEntryYear DESC, JournalEntyMonth DESC

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources