return count for all groups

[nikky]

Hi,
I want to get a recordcourt for all the users within eachstate; so, if I sort the results using a "group" parameter in Cfoutput, I would like the number of records in each state to be counted. Using my current query, I just get the total number of registered users in all the states, not just in each state:

<cfoutput Query="GetUsers" group="ByState">
<Font face="helvetica,arial,verdana" size="-1"><b><img

src="/pics/redarrow.jpg" border=0>State: #State#
&nbsp;&nbsp;Number:&nbsp;#GetUsers.RecordCount#</b><br>

 

[r937]

<cfoutput Query="GetUsers" group="ByState">
  <cfset state_counter = 0 >
  <cfoutput>
    <cfset state_counter = state_counter + 1 >
  </cfoutput>
State: #ByState# Rows: #state_counter#
</cfoutput>

r937.com | rudy.ca

 

[philhege]

Using a grouping query is a better idea. Ask the DB to return the state and count for each:

SELECT state, count(state) as user_count
FROM MyUserTable
WHERE [your clause here]
GROUP by state

Then the display part is a simple CFOUTPUT.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.