cfpoutput query group

[trojnfn]

I posted something like this previously and thought I got it to work, but not it does not work.
I have a table with state, cities within each state, and multiple codes for each city.

For each state, I want to list all the cities within each state alphabetically, and then the city codes and count for each city.

The output should look like this :

California
Los Angeles
1 (150)
4 (35)

San Diego
1 (350)
4(433)

San Francisco
1 (67)

etc...

This is what I am getting, using the code below :

California
Los Angeles
1 (150)
San Diego
1 (350)
San Francisco 1 (67)
Los Angeles
4 (35)
San Diego
4 (443)

<cfoutput query="qry" group="state">
<td valign="top">
<b>#state#</b>
<br>
<cfoutput group="city">
#city#
<br>
<cfoutput>
<cfif citgy_code is "4">
<a href="link goes here>#city_code# (#totalcount#)</a><br>
<cfelseif city_code is "1">
<a href="link goes here">#city_code# (#totalcount#)</a><br>
</cfif>
</cfoutput>
</cfoutput>
</td>
</cfoutput>

What do I need to do with the above code to group all of the cities together, listing all of the codes, instead of having the cities separated by the code ?

Thanks for any help.

[Dan_Bracuk]

What's the query?

[Dan_Bracuk]

What's the query?

[trojnfn]

the query is somewhat complex (for me) and involves numerous views, so somebody else wrote it.

I just need to know how to format the output properly, based on the code and example I provided. Do I have the table tags in the wrong area, or the cfoutput group in the wrong area ?

[trojnfn]

the query is somewhat complex (for me) and involves numerous views, so somebody else wrote it.

I just need to know how to format the output properly, based on the code and example I provided. Do I have the table tags in the wrong area, or the cfoutput group in the wrong area ?

[Newsgroup_User]

you query code is needed because the output code looks correct and
should output the data the way you want it. since it does not, that
means your query does not pull the expected data, or rather it likely
groups the data in a particular way.
to adjust your code to the way the query returns the data we need to see
the query.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[trojnfn]

Here is my actual code for the query and output, the other was an example to provide better clarity.

<cfquery name="qry" datasource="datasource">
SELECT count(a.topNumber) as totalcount, topNumber AS ActivityID, region,site, site_ID, c.activity_type
FROM dbo.get_max_activityID a
INNER JOIN dbo.get_join_ref_Site b
ON a.ref_number = b.ref_number and region = 'California'
INNER JOIN dbo.Activity_Master c
ON a.topNumber = c.Activity_ID
where topnumber = '1' or topnumber = '4'
group by topNumber, b.region, b.site, b.site_id, c.activity_type
</cfquery>

<cfoutput query="qry" group="region">
<td valign="top">
          <b>#region#</b><br>
<cfoutput group="site">
            
   #site#<br>
<cfoutput>               
    
<cfif activityid is "4">
<a href="receiving.cfm?region=#region#&site=#site#&activity_id=#activityid#&activity_type=#activity_type#">
#activity_type# (#totalcount#)</a><br>
<cfelseif activityid is "1">
<a href="receiving.cfm?region=#region#&site=#site#&activity_id=#activityid#&activity_type=#activity_type#">
#activity_type# (#totalcount#)</a><br>
</cfif>
</cfoutput>
</cfoutput>
</td>
</cfoutput>

[Dan_Bracuk · Accepted solution]

The likely cause of your problem is the lack of an order by clause in your query.