Loop a Result Set Within a StoredProc

[Warden]

I am trying to setup a storedproc that first joins a few tables in order to
pull a select type of member from a master members list. I then want to take
the loop that result set to get a count of where these members are from I have
the first part down where we group by country then DISTINCT the state/province.
Now I just need to run a second similar statement that will get the per state
count. Results are set to output like such

United States
CA - 300 members
NY - 200 members

Canada
MT - 50

ETC. ETC.

Below is my SP code I have so far. Any help is greatly apprecitated.

CREATE PROCEDURE sp_user_report

AS

SELECT DISTINCT
act_address.state,
globals.dbo.country.country_name,
globals.dbo.country.display_order
FROM
actor_access_user INNER JOIN act_actor ON
actor_access_user.act_actor_id=act_actor.actor_id
INNER JOIN act_address ON act_actor.address_id=act_address.address_id
INNER JOIN globals.dbo.country ON
act_address.country_id=globals.dbo.country.country _id
GROUP BY
act_address.country_id

ORDER BY
globals.dbo.country.display_order
GO

[mxstu]

I don't think you need a loop. You should be able to get a per state count, by
making a slight modification to your SELECT DISTINCT Query

--- SQL is not tested
SELECT c.country_name, addr.state, COUNT(*) AS TotalMembers
FROM actor_access_user usr INNER JOIN act_actor act ON usr.act_actor_id =
act.actor_id
INNER JOIN act_address addr ON act.address_id = addr.address_id
INNER JOIN globals.dbo.country c ON addr.country_id = c.country_id
GROUP BY c.country_name, addr.state

You could then use a CFOUPUT "group" to produce these results

United States
CA - 300 members
NY - 200 members

Canada
MT - 50