"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.
>
>

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.

"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

Dan Slaby wrote:
> 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