counting by month - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

counting by month

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-10-07 : 09:14:10

Hi,
I have queried a table d_jobattributes and added a Month column
which depends on the create date. This bit is fine.
I then want to perform a count by month, this bit does
not quite work, I receive the following error msg:

Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'group'.

select month,COUNT(jobid)
from
(

Select JobID, 'Month' =
CASE
When CreateDate >='2010-01-01' and CreateDate <='2010-01-31' THEN 1
When CreateDate >='2010-02-01' and CreateDate <='2010-02-28' THEN 2
When CreateDate >='2010-03-01' and CreateDate <='2010-03-31' THEN 3
When CreateDate >='2010-04-01' and CreateDate <='2010-04-30' THEN 4
When CreateDate >='2010-05-01' and CreateDate <='2010-05-31' THEN 5
When CreateDate >='2010-06-01' and CreateDate <='2010-06-30' THEN 6
When CreateDate >='2010-07-01' and CreateDate <='2010-07-31' THEN 7
When CreateDate >='2010-08-01' and CreateDate <='2010-08-31' THEN 8
When CreateDate >='2010-09-01' and CreateDate <='2010-09-30' THEN 9
When CreateDate >='2010-10-01' and CreateDate <='2010-10-31' THEN 10
When CreateDate >='2010-11-01' and CreateDate <='2010-11-30' THEN 11
When CreateDate >='2010-12-01' and CreateDate <='2010-12-31' THEN 12
END

from D_JobAttributes

where CreateDate >='2010-01-01' and CreateDate <='2010-12-31' and LEID in ('02001','02010')

and RecordTypeID >2
)
group by MONTH

Thank you
Tariq

Eddie M
Starting Member

14 Posts

Posted - 2011-10-07 : 09:22:43

You need to add an alias to the derived table.

In other words, change this bit of code:


and RecordTypeID >2
)
group by MONTH

to this:


and RecordTypeID >2
) xx
group by MONTH

where xx is the alias. It doesn't matter what you put here, nor does it matter that you won't be using the alias to reference any columns. But the syntax requires that you always include it for a derived table.

Eddie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources