3 dimensial array or query in query? Not sure

[DeZiner]

I use the following code to output a list of city, state combinations. What I am looking to add is the number of establishments in each city and display it in () at the end of each row.

Care of Calista:
<!--- setting list of cities to display--->

<cflock TIMEOUT=&quot;30&quot; NAME=&quot;#Session.SessionID#&quot; type=&quot;Exclusive&quot;>
<!--- Set a variable for the record count(You'll need it later.) --->
<CFSET Session.Counter=&quot;#find_bnb.RecordCount#&quot;>
<!--- Create a new array. --->
<CFSET Session.QueryResults=ArrayNew(2)>
<!--- This is where you populate the array with the query results. --->
<CFLOOP INDEX=&quot;IdxOne&quot; FROM=&quot;1&quot; TO=&quot;#Session.Counter#&quot;>

<CFSET Session.QueryResults[IdxOne][1]=&quot;#find_bnb.city[IdxOne]#&quot;>
<CFSET Session.QueryResults[IdxOne][2]=&quot;#find_bnb.state[IdxOne]#&quot;>
</CFLOOP>
</cflock>

At this point do I run a query in a loop checking the count of establishments in the city/state combo?
Do I store those results in this same array or a new one?
DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

 

[GunJack]

I'm not sure I understand what you're looking to do with this. Does the query contain the number of establishments or does this come from somewhere else? If you could explain a little more about where the data comes from and how it's formatted, I should be able to help.

GJ

 

[DeZiner]

When a user clicks on a state or types in a city I run a query that returns the resuls as city, state. IE: Los Angeles, CA.

I would then like to find out how many establishments total in each city/state combo to display the results as:

Los Angeles, Ca (12)
Santa Barbara, CA (3)
Fullerton, CA (9)

I can get the query to show me the number of establishments in the first city/state that is returned but not the rest.

I imagine I need to loop through the city and states that were returned to figure out the number of places in each combo.

The user only queries by name or zip or city name and can choose to enter the state along with the above. DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

 

[GunJack]

Do you have an entry in the table for each establishment? If so, I think you can do what you want like this.

select * from table1
order by state asc, city asc

<cfoutput query=&quot;q1&quot; group=&quot;state,city&quot;>
<cfoutput>
<cfset x=0>
<cfoutput>
<cfset x=x+1>
</cfoutput>
#state#, #city# (#x#)
</cfoutput>
</cfoutput>

I haven't done anything with multiple groupings in a while but I think that's the right syntax.

Just let me know if that won't work.
GJ

 

[pcorreia]

GunJack, that's a good CF-side solution, but here's a more efficient way to do the same thing in SQL:

SELECT 
   t.city,
   t.state,
   COUNT(t.establishment) AS establishments
FROM
   tableName t
GROUP BY
   t.city,
   t.state