counting number of records within a query

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

counting number of records within a query

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2010-10-26 : 08:37:16

This is my query which works fine:

select UserName,ReportID,ReportCount,PeriodKey,b.BUKey,c.TerritoryName
from F_BIUsers_Reports a
inner join D_BIUsers b on a.UserKey = b.UserKey
inner join D_BusinessUnits c on b.BUKey = c.BUKey
where PeriodKey =94
and c.Year = 2010
order by ReportCount desc

I now want to count the number of records which this query generates and have tried the following (without success)

count (*) from
(
select UserName,ReportID,ReportCount,PeriodKey,b.BUKey,c.TerritoryName
from F_BIUsers_Reports a
inner join D_BIUsers b on a.UserKey = b.UserKey
inner join D_BusinessUnits c on b.BUKey = c.BUKey
where PeriodKey =94
and c.Year = 2010
order by ReportCount desc
)

Many thanks for your help on this

Kristen
Test

22859 Posts

Posted - 2010-10-26 : 08:51:25

SELECT count (*) from
(
select 1 ~~UserName,ReportID,ReportCount,PeriodKey,b.BUKey,c.TerritoryName~~
from F_BIUsers_Reports a
inner join D_BIUsers b on a.UserKey = b.UserKey
inner join D_BusinessUnits c on b.BUKey = c.BUKey
where PeriodKey =94
and c.Year = 2010
~~order by ReportCount desc~~
) AS X

Or add this immediately after the first query:

SELECT @@ROWCOUNT AS MyCount

Or change to become:

SELECT count (*)
from F_BIUsers_Reports a
inner join D_BIUsers b on a.UserKey = b.UserKey
inner join D_BusinessUnits c on b.BUKey = c.BUKey
where PeriodKey =94
and c.Year = 2010

tariq2
Posting Yak Master

125 Posts

Posted - 2010-10-26 : 10:00:35

Thank you Tristan, that is extremely helpful. thanks

tariq2
Posting Yak Master

125 Posts

Posted - 2010-10-26 : 10:09:10

Sorry, its Kristen :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources