Webmaster Forum / ColdFusion / Advanced Techniques / June 2005

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