MSSQL Stored Procedure Syntax?

Can't add Group By, but I'm pretty sure I need it.

Page 1 of 1

2 Replies - 2000 Views - Last Post: 24 November 2009 - 06:35 AM

#1 neals  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 02-November 09

MSSQL Stored Procedure Syntax?

Posted 23 November 2009 - 11:48 AM

CREATE PROC sp__News__NewsPerWeekInYear
@Year int
AS
	SELECT 
		DATEPART(WK,DatePosted) AS WeekNumber, 
		COUNT(NewsID) AS NewsPosts
	FROM News
	WHERE DATEPART(YYYY, dbo.News.DatePosted) = @Year
	ORDER BY WeekNumber
GO


Returns the error

Msg 8120, Level 16, State 1, Procedure sp__News__NewsPerWeekInYear, Line 5
Column 'News.DatePosted' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


But placing a Group By WeekNumber doesn't seem to help...

I'm a beginner at T-SQL, so excuse me if my error is especially ignorant.

Is This A Good Question/Topic? 0
  • +

Replies To: MSSQL Stored Procedure Syntax?

#2 Qrueger  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 19-November 09

Re: MSSQL Stored Procedure Syntax?

Posted 24 November 2009 - 12:14 AM

View Postneals, on 23 Nov, 2009 - 10:48 AM, said:

CREATE PROC sp__News__NewsPerWeekInYear
@Year int
AS
	SELECT 
		DATEPART(WK,DatePosted) AS WeekNumber, 
		COUNT(NewsID) AS NewsPosts
	FROM News
	WHERE DATEPART(YYYY, dbo.News.DatePosted) = @Year
	ORDER BY WeekNumber
GO


Returns the error

Msg 8120, Level 16, State 1, Procedure sp__News__NewsPerWeekInYear, Line 5
Column 'News.DatePosted' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


But placing a Group By WeekNumber doesn't seem to help...

I'm a beginner at T-SQL, so excuse me if my error is especially ignorant.

have you tried group by DatePosted ?
don't worry because it's still grouping by your datepart function.
Was This Post Helpful? 0
  • +
  • -

#3 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 267
  • View blog
  • Posts: 1,470
  • Joined: 07-April 08

Re: MSSQL Stored Procedure Syntax?

Posted 24 November 2009 - 06:35 AM

Try the following.
CREATE PROC sp__News__NewsPerWeekInYear
@Year int
AS
	SELECT 
		DATEPART(WK,DatePosted) AS WeekNumber, 
		COUNT(NewsID) AS NewsPosts
	FROM News
	WHERE DATEPART(YYYY, dbo.News.DatePosted) = @Year
		GROUP BY DatePosted
	ORDER BY WeekNumber
GO


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1