help with group by - 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

help with group by

Author

Topic

BendJoe
Posting Yak Master

128 Posts

Posted - 2008-11-24 : 22:01:51

I have a two tables.
Articles.

ArticleID,Title,Date

ArticleComments

ArticleCommentID,ArticleID,Comment

I need to get ArticleID,Title,count(ArticleCommentID) from the two tables I need to use a group by and am having trouble with it could someone help.

Select ArticleID.Title,(Select count(ArticleCommentID) from ArticleComments group by ArticleID) as CommentCount from Articles.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-24 : 22:32:13

select Ar.Articleid,Ar.Title,count(Ac.ArticleCommentID)as CountArticle from
Articles Ar inner join
ArticleComments Ac
on Ar.ArticleID =Ac.ArticleId
group by Ar.Articleid,Ar.Title

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-11-26 : 00:10:46

SELECT articleid, title,(select count(articlecommentid) from articlecomments WHERE articleid = a.articleid ) from articles a

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 00:21:26

quote:
Originally posted by bklr

SELECT articleid, title,(select count(articlecommentid) from articlecomments WHERE articleid = a.articleid ) from articles a

for large data the subquery will perform quite poorly. So join method posted by Sodeep is better

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources