Error when run procedure - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Error when run procedure

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-04-03 : 13:48:55

Hello,

I am getting the error:
'dbo.Posts.PostId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

On code:
SELECT
PostId = bp.PostId,
PostTitle,
PostContent,
PostUpdatedDate,
PostIsPublished,
PostCommentsAreAllowed,
UserName AS PostAuthorName,
UserEmail AS PostAuthorEmail,
AVG(PostRate) AS PostRating,
COUNT(PostCommentId) As NumberOfBlogPostComments
FROM dbo.Posts bp
INNER JOIN dbo.Users bu ON bu.UserId = bp.AuthorId
INNER JOIN dbo.PostRatings bpr ON bpr.PostId = bp.PostId
INNER JOIN dbo.PostComments bpc ON bpc.PostId = bp.PostId
WHERE bp.BlogId = @BlogId
ORDER BY PostUpdatedDate ASC

I am not getting what I have wrong in my code.
I tried various changes but the best I could get was:
Ambiguous column name 'BlogPostId'.

When I made:
SELECT
PostId,
...

Thank You,
Miguel

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-03 : 14:01:32

You need to learn about using aggregate functions in the SELECT statement. You cannot just use a COUNT in any statement because it aggregates all the rows into summary rows and that introdices the problem of what to do with all of the values from the other columns in the SELECT.

Take a look at SELECT in Books Online and take a look at the SELECT statement, GROUP BY and aggregate functions here
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Kristen
Test

22859 Posts

Posted - 2007-04-03 : 14:07:46

Guessing which columns go with which tables this might be what you are looking for. However, for a large recordset (more than a couple of hundred results) performance will probably be completely crap)


SELECT 
	PostId = bp.PostId,
	PostTitle,
	PostContent,
	PostUpdatedDate,
	PostIsPublished,
	PostCommentsAreAllowed,
	UserName AS PostAuthorName,
	UserEmail AS PostAuthorEmail,
	[PostRating] = (SELECT AVG(PostRate) FROM dbo.PostRatings AS bpr WHERE bpr.PostId = bp.PostId),
	[NumberOfBlogPostComments] = (SELECT COUNT(PostCommentId) FROM dbo.PostComments AS bpc WHERE bpc.PostId = bp.PostId)
FROM	dbo.Posts bp
	INNER JOIN dbo.Users AS bu 
		ON bu.UserId = bp.AuthorId
	INNER JOIN dbo.PostRatings AS bpr 
		ON bpr.PostId = bp.PostId
	INNER JOIN dbo.PostComments AS bpc 
		ON bpc.PostId = bp.PostId
WHERE	bp.BlogId = @BlogId
ORDER BY PostUpdatedDate ASC

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-04-03 : 14:28:52

Hi Kristen,

I had two procedures which for a given PostId would return the total number of comments and the average rating.
The code in each procedure is basically the 2 code lines you added:

[PostRating] = ...
[NumberOfBlogPostComments] = ...

So I just need to call these procedures from my procedure ...
... I just though I should do this with Inner Join

quote:
Originally posted by Kristen

However, for a large recordset (more than a couple of hundred results) performance will probably be completely crap)[code]

You are talking about what I was trying to do (using Inner Join) or the solution you gave me? I didn't understand ...

Thank You,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-03 : 14:59:14

Or you could try derived tabled if there are many records

SELECT		bp.PostId,
		bp.PostTitle,
		bp.PostContent,
		bp.PostUpdatedDate,
		bp.PostIsPublished,
		bp.PostCommentsAreAllowed,
		bu.UserName AS PostAuthorName,
		bu.UserEmail AS PostAuthorEmail,
		pr.PostRating,
		pc.NumberOfBlogPostComments
FROM		dbo.Posts bp
INNER JOIN	dbo.Users AS bu ON bu.UserId = bp.AuthorId
LEFT JOIN	(
			SELECT		PostId,
					AVG(PostRate) AS PostRating
			FROM		dbo.PostRatings
			GROUP BY	PostID
		) AS pr ON pr.PostID = bp.PostID
LEFT JOIN	(
			SELECT		PostID,
					COUNT(*) AS NumberOfBlogPostComments
			FROM		dbo.PostComments
			GROUP BY	PostID
		 ) AS pc ON pc.PostID = bp.PostID
WHERE		bp.BlogId = @BlogId
ORDER BY	bp.PostUpdatedDate

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-04-04 : 01:34:03

quote:

quote:
Originally posted by Kristen

However, for a large recordset (more than a couple of hundred results) performance will probably be completely crap)

You are talking about what I was trying to do (using Inner Join) or the solution you gave me? I didn't understand ...

I was talking about my proposed solution, in red. That style tends to be slow for large resultsets (i.e. WHERE bp.BlogId = @BlogId matches lots of rows)

If you try Peso's method (which is much nicer!) I would constrain the derived tables to reduce the CPU effort to create them:
[code]
SELECT bp.PostId,
bp.PostTitle,
bp.PostContent,
bp.PostUpdatedDate,
bp.PostIsPublished,
bp.PostCommentsAreAllowed,
bu.UserName AS PostAuthorName,
bu.UserEmail AS PostAuthorEmail,
pr.PostRating,
pc.NumberOfBlogPostComments
FROM dbo.Posts AS bp
INNER JOIN dbo.Users AS bu ON bu.UserId = bp.AuthorId
LEFT JOIN (
SELECT PostId,
AVG(PostRate) AS PostRating
FROM dbo.PostRatings AS bpr
INNER JOIN dbo.Posts bp
ON bpr.PostId = bp.PostId
AND bp.BlogId = @BlogId
GROUP BY PostID
) AS pr ON pr.PostID = bp.PostID
LEFT JOIN (
SELECT PostID,
COUNT(*) AS NumberOfBlogPostComments
FROM dbo.PostComments AS bpc
INNER JOIN dbo.Posts AS bp
ON bpc.PostId = bp.PostId
AND bp.BlogId = @BlogId
GROUP BY PostID
) AS pc ON pc.PostID = bp.PostID
WHERE bp.BlogId = @BlogId
ORDER BY bp.PostUpdatedDate

Kristen

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-04-04 : 11:33:40

Hi Peter and Kristen,

In the mean while I came up with the following solution:


SELECT 
  PostId = bp.PostId,
  PostTitle,
  PostContent,
  PostUpdatedDate,
  PostIsPublished,
  PostCommentsAreAllowed,
  UserName AS PostAuthorName,
  UserEmail AS PostAuthorEmail,
  COALESCE(AveragePostRate, 0) AS PostRating,
  PostCount AS NumberOfPostComments
FROM dbo.Posts bp
INNER JOIN dbo.Users bu ON bu.UserId = bp.AuthorId
CROSS APPLY (SELECT AVG(PostRate) AS AveragePostRate
FROM dbo.PostRatings AS pr
WHERE pr.PostId = bp.PostId) AS bpr 
CROSS APPLY (SELECT COUNT(*) AS PostCount
FROM dbo.PostComments AS pc
WHERE pc.PostId = bp.PostId) AS bpc
WHERE bp.BlogId = @BlogId
ORDER BY PostUpdatedDate ASC

I am using CROSS APPLY.
Should use INNER JOIN instead?

I think CROSS APLLY is only for SQL2005 but that is what I am using.

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 11:38:02

CROSS APPLY should be used with table-valued UDF, having it's parameters from table itself.

Mean while? You mean 9 hours after Kristens suggestion.
BUT... I think it is good you try for yourself, and extend your limitiations of T-SQL

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-04-04 : 11:54:48

quote:
Originally posted by Peso

Mean while? You mean 9 hours after Kristens suggestion.
BUT...

Hi Peter,

I didn't receive any notifications of the last 3 or 4 posts.
So only now when I was going to post my Cross Apply code I realized that I had those answers ...

Thanks,
Miguel

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-04-04 : 13:26:41

Hello Peter,

Just one more question about this.
I am trying to add a column named PostLabels with all labels separated by a comma. I get LabelId from LabelsInPosts for each PostId and then i take all LabelNames from table LabelNames.

I based my code on some code you sent me before (In that case LabelNames were not taken from a different table).


SELECT		
  bp.PostId,
  bp.PostTitle,
  ...
  bp.PostLabels
FROM dbo.Posts AS bp
  ...
STUFF((
  SELECT ',' + bpl.LabelName
  FROM (
    SELECT DISTINCT bl.LabelName
    FROM dbo.LabelsInPosts blip
    INNER JOIN dbo.Labels AS bl
    ON bl.LabelId = blip.LabelId
    WHERE blip.PostId = p.PostId) AS bpl
  ORDER BY bpl.LabelName
  FOR XML PATH('')), 1, 1, '') AS PostLabels
WHERE bp.BlogId = @BlogId
ORDER BY bp.PostUpdatedDate

I get the error:
Incorrect syntax near 'STUFF'.
It is not specified where exactly.

I think my problem is of integration STUFF in my code.
But I am not completely sure. I made some changes on your suggestion.

Thank You,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 13:30:41

All column to select, real or virtual, must occur before the FROM clause

SELECT		
  bp.PostId,
  bp.PostTitle,
  ...
,STUFF((
  SELECT ',' + bpl.LabelName
  FROM (
    SELECT DISTINCT bl.LabelName
    FROM dbo.LabelsInPosts blip
    INNER JOIN dbo.Labels AS bl
    ON bl.LabelId = blip.LabelId
    WHERE blip.PostId = p.PostId) AS bpl
  ORDER BY bpl.LabelName
  FOR XML PATH('')), 1, 1, '') AS PostLabels
FROM dbo.Posts AS bp
  ...
WHERE bp.BlogId = @BlogId
ORDER BY bp.PostUpdatedDate

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 13:32:11

It seems to me you are way, way, over your head with this project.
Is there a chance you can start with a smaller, simpler, project to learn T-SQL?

We have written every aspect of your code in your "blog-project".

Peter Larsson
Helsingborg, Sweden

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-04-05 : 08:45:28

quote:
Originally posted by Peso

It seems to me you are way, way, over your head with this project.
Is there a chance you can start with a smaller, simpler, project to learn T-SQL?

As I said I work mostly in .NET and other areas and I ended up with doing the SQL part because of circumstances. I needed to have this done.

Now, everything is done ... I think ...

But getting into T-SQL is what I am doing next ... from the start.

Thank You for the Help,
Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources