Thursday, March 22, 2012

Count

Hello,

I need to retrieve all records from a table named Blogs and the number
of Posts associated with which Blog giving the name NumberOfPosts to
that extra column.

I have the following:

SELECT b.*, p.COUNT(*) AS NumberOfPosts
FROM dbo.Blogs b
LEFT JOIN dbo.Posts p
ON b.BlogId = p.BlogId

I get the error:
Incorrect syntax near '*'.

Could someone, please, help me out?

Thanks,
Miguel

Try this:

SELECT b.*, COUNT(p.*) AS NumberOfPosts
FROM dbo.Blogs b
LEFT JOIN dbo.Posts p
ON b.BlogId = p.BlogId

|||

I think you need agroup by clause to use the Count(*), do you have to return all the columns in dbo.Blogs?

|||

And change the top of your query to...

SELECT b.*, COUNT(*) AS NumberOfPosts

No comments:

Post a Comment