Help with 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)

Help with Count

Author

Topic

fizzer
Starting Member

10 Posts

Posted - 2008-12-30 : 06:52:07

Hi,

I have two tables (Articles and Categories). I have a stored proc that returns a list of categories, which is just a simple select. However, I also want to return the number of articles in each Category returned.

I've tried using Count in my select but I don't think i'm on the right track here. I've got a CategoryID field in my Articles table so an Article can only be in one category. How do I do this?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-30 : 06:55:31

[code]select categorycolumn,count(articlecolumn)
from
categorytable a join articletable b on a.categoryid=b.categoryid
group by categorycolumn[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-30 : 07:03:08

select CategoryID ,count(distinct aritcles) from articles as art
inner join Categories as c on ( c.categoryid = art.categoryid)
group by categoryid

fizzer
Starting Member

10 Posts

Posted - 2009-01-02 : 05:58:36

Thanks. However, I have another problem. The following works as expected

SELECT c.CategoryID, c.CategoryName,
c.ParentID, count(a.articleID) as ArticleCount
FROM Categories c
LEFT OUTER JOIN Articles a
ON a.CategoryID = c.categoryID
WHERE c.ParentID = @ParentID
GROUP BY c.CategoryID, c.CategoryName, c.parentID

I want to add c.CategoryDescription to my select but I can't because it is a text field. How do I get round this?? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 06:36:10

cast it to varchar(max) and try

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-02 : 06:57:23

SELECT c.CategoryID, c.CategoryName,convert( varchar(max),c.CategoryDescription ) as CategoryDescription
c.ParentID, count(a.articleID) as ArticleCount
FROM Categories c
LEFT OUTER JOIN Articles a
ON a.CategoryID = c.categoryID
WHERE c.ParentID = @ParentID
GROUP BY c.CategoryID, c.CategoryName, c.parentID,convert( varchar(max),c.CategoryDescription )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 08:48:14

quote:
Originally posted by bklr

SELECT c.CategoryID, c.CategoryName,convert( varchar(max),c.CategoryDescription ) as CategoryDescription
c.ParentID, count(a.articleID) as ArticleCount
FROM Categories c
LEFT OUTER JOIN Articles a
ON a.CategoryID = c.categoryID
WHERE c.ParentID = @ParentID
GROUP BY c.CategoryID, c.CategoryName, c.parentID,convert( varchar(max),c.CategoryDescription )

how is this different from what i suggested?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-02 : 11:13:44

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

SELECT c.CategoryID, c.CategoryName,convert( varchar(max),c.CategoryDescription ) as CategoryDescription
c.ParentID, count(a.articleID) as ArticleCount
FROM Categories c
LEFT OUTER JOIN Articles a
ON a.CategoryID = c.categoryID
WHERE c.ParentID = @ParentID
GROUP BY c.CategoryID, c.CategoryName, c.parentID,convert( varchar(max),c.CategoryDescription )

how is this different from what i suggested?

hi visakh,
just i given query & then i used the convert function
we can use cast also

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources