Barry,
Try this:
<cfquery name="Zip" datasource="#dsn#">
SELECT a.zipcode, f.fdd_zip, f.fdd_name, a.IsPublished, a.IsActive,
f.fdd_state, count(distinct a.zipcode) as zip_count
FROM articles a, fddirectory f
WHERE a.zipcode = f.fdd_zip
AND f.fdd_state = <cfqueryparam cfsqltype="cf_sql_varchar"
value="MD">
GROUP BY a.zipcode, f.fdd_zip, f.fdd_name, a.IsPublished, a.IsActive,
f.fdd_state
ORDER BY (whatever order you want them)
</cfquery>
Let me know if this works or not for you. Not sure if it will work right in
mySQL or not. If not, try this then:
<cfquery name="Zip" datasource="#dsn#">
SELECT a.zipcode, f.fdd_zip, f.fdd_name, a.IsPublished, a.IsActive,
f.fdd_state
FROM articles a, fddirectory f
WHERE a.zipcode = f.fdd_zip
AND f.fdd_state = <cfqueryparam cfsqltype="cf_sql_varchar"
value="MD">
ORDER BY (whatever order you want them)
</cfquery>
<cfquery dbtype="query" name=" qZipTotals ">
Select zipcode,count(*) as zip_count
>From Zip
Group by zipcode
Order by zipcode
</cfquery>
... now inside your loop, do this:
<cfloop query="Zip">
<cfquery dbtype="query" name="qGetThisZipCount">
Select zip_count from qZipTotals
Where zipcode = <cfqueryparam value="#Zip.zipcode#"
cfsqltype="cf_sql_varchar">
</cfquery>
<cfoutput>#fdd_name# (#qGetThisZipCount.zip_count#)<br></cfoutput>
</cfloop>
This is using query of queries so you don't have to hit the database twice.
There are a number of ways to accomplish this.
Dave Phillips
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:330759
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
