Just add:
(select count(*) from articles where zipcode = fddirectory.fdd_zip) as
articleCount
to the end of your SELECT clause:
SELECT fddirectory.fdd_id, fddirectory.fdd_name, fddirectory.fdd_zip,
fddirectory.fdd_state, states.abrev
, (select count(*) from articles where zipcode = fddirectory.fdd_zip)
as articleCount
FROM fddirectory, states
WHERE fddirectory.fdd_state = <cfqueryparam value="#statecodeID#"
cfsqltype="CF_SQL_VARCHAR">
AND fddirectory.fdd_state = states.abrev
ORDER BY fddirectory.fdd_name
That should do it for you,
cheers,
barneyb
On Mon, Feb 15, 2010 at 8:50 AM, Barry Mcconaghey <[email protected]> wrote:
>
> Hello Everybody.
>
> I have been thinking and working on this for three days now. I'm looking for
> some help totaling two fields from two tables.
>
> Here is what I have:
>
> Sample Tables:
>
> Table - fdd_directory
>
> fdd_name (Example: ACME Fire Dept)
> fdd_state (Example: PA)
> fdd_zip (Example: 12345)
>
> Table - states
>
> statecodeID (Example: 111)
> abrev (Example: PA)
> statename (Example: Pennsylvania)
>
> Table - articles
>
> zipcode (Example: 12345)
>
> <!---Here is what I have:--->
>
> <cfquery name="states" datasource="#dsn#">
> SELECT count(fddirectory.fdd_id) AS FDCount, states.statename,
> states.abrev, states.statecodeID
> FROM fddirectory, states
> WHERE fddirectory.fdd_state = states.abrev
> GROUP BY statename
> ORDER BY statename
> </cfquery>
>
> <cfoutput query="states">
> <a href="next.cfm?statecodeID=#abrev#">#states.statename#
> (#NumberFormat(fdcount)#)</a><br />
> </cfoutput>
>
> So far, so good...
>
> Next.cfm
>
> <cfquery name="Story" datasource="#dsn#">
> SELECT fddirectory.fdd_id, fddirectory.fdd_name, fddirectory.fdd_zip,
> fddirectory.fdd_state, states.abrev
> FROM fddirectory, states
> WHERE fddirectory.fdd_state = <cfqueryparam value="#statecodeID#"
> cfsqltype="CF_SQL_VARCHAR">
> AND fddirectory.fdd_state = states.abrev
> ORDER BY fddirectory.fdd_name
> </cfquery>
>
> <!---Here is where I would like to total articles.zipcode =
> fddirectory.fdd_zip--->
>
> <cfoutput query="Story">
> <a href="next1.cfm?fdd_id=#story.fdd_id#">#story.fdd_name#</a> (Total
> Here)<br>
> </cfoutput>
>
> "Total Here" should equal articles.zipcode = fddirectory.fdd_zip or the
> number of article(s) from the articles table that match each fdd_name.
>
> For Example:
>
> ACME FD (Total 38)
>
> Thanks,
>
> Barry
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:330742
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
