Count - 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)

Count

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-26 : 17:36:14

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
INNER JOIN dbo.Posts p
ON b.BlogId = p.BlogId

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

Could someone, please, help me out?

Thanks,
Miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 17:40:12

select b.*, coalesce(b.c, 0) as numberofposts
from blogs as b
left join (select blogid, count(*) as c from posts group by blogid) as y on y.blogid = b.blogid

E 12°55'05.25"
N 56°04'39.16"

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-09-26 : 17:54:55

Hi Peso,

Thanks!

By the way, you mean:
coalesce(y.c, 0)

right?

Thanks,
Miguel

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 02:03:11

"By the way, you mean:
coalesce(y.c, 0)"

Yes, he did.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources