How to rowcount using groupby? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to rowcount using groupby?

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2006-05-11 : 07:38:20

I am writing a query where i need to use groupby and want my procedure to return number of rows returned by the select statement.My query is like

SELECT tblReviewRequestDetails.RequestID
FROM tblPortfolioTemplate INNER JOIN
tblReviewRequest ON tblPortfolioTemplate.PortfolioId = tblReviewRequest.PortFolioID INNER JOIN tblReviewRequestDetails ON tblReviewRequest.RequestId = tblReviewRequestDetails.RequestID INNER JOIN users ON tblReviewRequest.SentBy = users.UserID WHERE tblReviewRequestDetails.Status=0 and tblReviewRequest.SentBy=228 group by tblReviewRequestDetails.RequestID

Now if the result after the query is run is
RequestID
326
345
347

Then,I want that I should get 3 as result i.e. a single number as result.some thing like

RequestIDCount
3

Any help would be appreciated.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 07:49:13

SELECT Count(tblReviewRequestDetails.RequestID)
FROM tblPortfolioTemplate INNER JOIN
tblReviewRequest ON tblPortfolioTemplate.PortfolioId = tblReviewRequest.PortFolioID INNER JOIN tblReviewRequestDetails ON tblReviewRequest.RequestId = tblReviewRequestDetails.RequestID INNER JOIN users ON tblReviewRequest.SentBy = users.UserID WHERE tblReviewRequestDetails.Status=0 and tblReviewRequest.SentBy=228 group by tblReviewRequestDetails.RequestID

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-11 : 07:49:39

After select statement run

Select @@RowCount

Madhivanan

Failing to plan is Planning to fail

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2006-05-11 : 08:29:39

Thanks for your replies.I have found solution
select count(*)as RequestId from(SELECT tblReviewRequestDetails.RequestID

FROM tblPortfolioTemplate INNER JOIN
tblReviewRequest ON tblPortfolioTemplate.PortfolioId = tblReviewRequest.PortFolioID INNER JOIN
tblReviewRequestDetails ON tblReviewRequest.RequestId = tblReviewRequestDetails.RequestID INNER JOIN
users ON tblReviewRequest.SentBy = users.UserID
WHERE tblReviewRequestDetails.Status=0 and tblReviewRequestDetails.RequestTo=@UserId
group by tblReviewRequestDetails.RequestID) RequestCount

Thanks again fo rur posts

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-11 : 08:32:20

why are you using subquery for this ..?? if you directly do the Count(tblReviewRequestDetails.RequestID ) it should work??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

tomy74
Starting Member

32 Posts

Posted - 2006-05-11 : 09:56:16

hi,

It's better for you to calculate the count by 1 query (for better performance) and more professional, so use SELECT Count(tblReviewRequestDetails.RequestID)
FROM tblPortfolioTemplate INNER JOIN .....

Thanks,

Tarek Ghazali
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources