Count re-occurances of entry - 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 2000 Forums

Transact-SQL (2000)

Count re-occurances of entry

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-24 : 07:25:54

Chris writes "I use SQL Server 2000 on Windows 2000. I use 2 tables for operating a mail list.

I need a solution to get the number of times that a entry re-appears in a given select statement. I have the following tables:

1) ArticleID, Title, Article
2) SentID, DateSent, ArticleID

I have joined these 2 tables so that I can have an easy output of what articles have been sent.

SELECT tblArticle.*, tblSentArticles.*
FROM tblArticle LEFT JOIN tblSentArticles ON tblArticle.ArticleID = tblSentArticles.ArticleID;

I need some kind of output that can show me how many times an article have been sent. Would it be possible to have some kind of function to do something like the example below

ArticleID, Title, Article, TotalOccurances
1, Article1, article..., 1
2, Article2, article..., 0
3, Article3, article..., 2

Kind regards
Chris"

mr_mist
Grunnio

1870 Posts

Posted - 2004-06-24 : 07:47:35

Group by is your friend.

-------
Moo. :)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-24 : 07:48:10

Something Like This:

select a.ArticleID, a.Title, a.Article, count(isnull(s.ArticleID, 0)) as TotalOccurances
FROM tblArticle a
JOIN tblsentarticles s
on a.ArticleID = s.ArticleID
GROUP BY a.ArticleID, a.Title, a.Article

Duane.

cdutoit
Starting Member

1 Post

Posted - 2004-07-06 : 17:31:48

Thanks Duane!

It was exactly what I was looking for!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-07 : 00:05:02

Hey Chris - SA Surname?

That was a bit of a delayed response there about 12 days.

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources