count total records - 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

SQL Server Development (2000)

count total records

Author

Topic

-Dman100-
Posting Yak Master

210 Posts

Posted - 2004-09-17 : 02:46:12

I'm trying to count the total records with a unique ID in two tables.

I was trying this query:

SELECT TOP 5 * , (SELECT COUNT(*) FROM dbo.UserComments) AS TotalRecords
FROM dbo.Weblog a JOIN dbo.UserComments b
ON a.BlogID = b.BlogID
WHERE a.blogID = b.BlogID
ORDER BY blogDate DESC

The total records field that was created is empty...no value is returned.

I stripped out the join and tried the following query:

SELECT TOP 5 * , (SELECT COUNT(*) FROM dbo.UserComments) AS TotalRecords
FROM dbo.Weblog
ORDER BY blogDate DESC

The total records field holds the count for all the records in the UserComments table. How can I get the count for the records specific to the "blogID" field in the two tables?

Thanks for any help.
-D-

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-17 : 03:43:29

When you alias your tables it gets more readable if you choose "better" aliases.
E.g Weblog as wl, UserComments AS uc or something like that.
And also I couldn't make out from your code if the column blogDate belongs to a or b ?

Now I will try to help You:

SELECT TOP 5 a.BlogID, a.blogDate, COUNT(*) AS TotalRecords
FROM dbo.Weblog a JOIN dbo.UserComments b
ON a.BlogID = b.BlogID
WHERE a.blogID = b.BlogID
GROUP BY a.BlogID, a.blogDate
ORDER BY a.blogDate DESC

... or ...

SELECT TOP 5 * , (SELECT COUNT(*) FROM dbo.UserComments uc2 WHERE uc2.BlogID = b.BlogID) AS TotalRecords
FROM dbo.Weblog a JOIN dbo.UserComments b
ON a.BlogID = b.BlogID
WHERE a.blogID = b.BlogID
ORDER BY blogDate DESC

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources