Number of records in a query

8 Jul 2005 5:00 AM

Dan Slaby

I'm sure this is a typical braintwister. However, I have a table that tracks
income by resident, source and quarter. I need to determine the total number
of persons who have been employed 6 months or more (>=2 quarters). I use a
quarter number system such as yyyyq (20052). When I run the following query
I get 84 rows:

Select count(residentid), ResidentID
From tblResidentIncome
Where IncomeSourceCode = 'G'
Group By ResidentID
Having count(IncomeSourceCode) >= 2

This query returns all residents who have an incomesourceCode of 'G' for
more than 2 quarters. How can I derive the number of rows returned (84) from
this query as an output parameter? Thanks.

8 Jul 2005 6:36 AM

Uri Dimant

Dan
See if this helps
USE pubs

DECLARE @RowCount int

EXEC sp_executesql
    N'SELECT @RowCount = COUNT(*) FROM authors',
     N'@RowCount int OUTPUT',
     @RowCount OUTPUT

RAISERROR ('Authors rowcount is %d', 0, 1, @RowCount)

Show quote

"Dan Slaby" <dls@newsgroup.nospam> wrote in message
news:uetatl3gFHA.1416@TK2MSFTNGP09.phx.gbl...
> I'm sure this is a typical braintwister. However, I have a table that
tracks
> income by resident, source and quarter. I need to determine the total
number
> of persons who have been employed 6 months or more (>=2 quarters). I use a
> quarter number system such as yyyyq (20052). When I run the following
query
> I get 84 rows:
>
> Select count(residentid), ResidentID
> From tblResidentIncome
> Where IncomeSourceCode = 'G'
> Group By ResidentID
> Having count(IncomeSourceCode) >= 2
>
> This query returns all residents who have an incomesourceCode of 'G' for
> more than 2 quarters. How can I derive the number of rows returned (84)
from
> this query as an output parameter? Thanks.
>
>

8 Jul 2005 7:02 AM

David Gugick

Dan Slaby wrote:
Show quote

> I'm sure this is a typical braintwister. However, I have a table that
> tracks income by resident, source and quarter. I need to determine
> the total number of persons who have been employed 6 months or more
> (>=2 quarters). I use a quarter number system such as yyyyq (20052).
> When I run the following query I get 84 rows:
>
> Select count(residentid), ResidentID
> From tblResidentIncome
> Where IncomeSourceCode = 'G'
> Group By ResidentID
> Having count(IncomeSourceCode) >= 2
>
> This query returns all residents who have an incomesourceCode of 'G'
> for more than 2 quarters. How can I derive the number of rows
> returned (84) from this query as an output parameter? Thanks.

Declare @TotalCount INT

Select
@TotalCount = COUNT(SubTotal)
From (
Select count(au_id) as "SubTotal"
From dbo.titleauthor
Group By au_id
Having Count(au_id) > 1) a

Select @TotalCount

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

8 Jul 2005 4:12 PM

Dan Slaby

David:

Here's the solution based on your suggestion. However, it doesn't work
unless the 'a' is appended to the end. Why is the 'a' necessary?

Declare @RowCount int
SELECT @RowCount = Count(SubTotal)
FROM (SELECT Count(ResidentID) as "SubTotal"
FROM tblResidentIncome
WHERE IncomeSourceCode = 'G'
Group By ResidentID
Having Count(ResidentID) >= 2)a
SELECT @RowCount

Thanks for the help.

Dan

Show quote

"David Gugick" <david.gugick-nospam@quest.com> wrote in message
news:eKus9u4gFHA.2152@TK2MSFTNGP14.phx.gbl...
> Dan Slaby wrote:
>> I'm sure this is a typical braintwister. However, I have a table that
>> tracks income by resident, source and quarter. I need to determine
>> the total number of persons who have been employed 6 months or more
>> (>=2 quarters). I use a quarter number system such as yyyyq (20052).
>> When I run the following query I get 84 rows:
>>
>> Select count(residentid), ResidentID
>> From tblResidentIncome
>> Where IncomeSourceCode = 'G'
>> Group By ResidentID
>> Having count(IncomeSourceCode) >= 2
>>
>> This query returns all residents who have an incomesourceCode of 'G'
>> for more than 2 quarters. How can I derive the number of rows
>> returned (84) from this query as an output parameter? Thanks.
>
> Declare @TotalCount INT
>
> Select @TotalCount = COUNT(SubTotal)
> From (
> Select count(au_id) as "SubTotal"
> From dbo.titleauthor
> Group By au_id
> Having Count(au_id) > 1) a
>
> Select @TotalCount
>
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

8 Jul 2005 5:15 PM

David Gugick

Dan Slaby wrote:
Show quote

> David:
>
> Here's the solution based on your suggestion. However, it doesn't work
> unless the 'a' is appended to the end. Why is the 'a' necessary?
>
> Declare @RowCount int
> SELECT @RowCount = Count(SubTotal)
> FROM (SELECT Count(ResidentID) as "SubTotal"
> FROM tblResidentIncome
> WHERE IncomeSourceCode = 'G'
> Group By ResidentID
> Having Count(ResidentID) >= 2)a
> SELECT @RowCount
>
> Thanks for the help.
>
> Dan

The alias is required (I just used "a" which is a bit meaningless)
because you are referencing columns in a subquery instead of from the
table directly.

--
David Gugick
Quest Software
www.imceda.com
www.quest.com