Total Two Field From Two Tables

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

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 ----- Excess quoted text cut - see Original Post for more -----

Thanks Barney B. ERROR Message: Syntax error or access violation: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select count(*) from articles where zipcode = fddirectory.fdd_z Here is the cfquery: <cfquery name="Story" datasource="#dsn#"> 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 </cfquery>   ----- Excess quoted text cut - see Original Post for more -----

What version of MySQL are you using?  If it's a really old one that doesn't support subqueries, check out the MySQL docs.  They have a section about rewriting subqueries into JOINs. cheers, barneyb ----- Excess quoted text cut - see Original Post for more -----

I'm using MySQL 4.0.20. Is there any other way to total these two fields? Barry >What version of MySQL are you using?  If it's a really old one that >doesn't support subqueries, check out the MySQL docs.  They have a >section about rewriting subqueries into JOINs. > >cheers, >barneyb

You just need a JOIN and GROUP BY clause.  Check out the subquery-to-join docs on MySQL's site. They have examples of how to do exactly what you want. cheers, barneyb ----- Excess quoted text cut - see Original Post for more -----

Thanks Barney! The JOIN and GROUP clause might be over my head. Do I use a RecordCount? Barry >You just need a JOIN and GROUP BY clause.  Check out the >subquery-to-join docs on MySQL's site. They have examples of how to do >exactly what you want. > >cheers, >barneyb

I think I'm getting closer. This code below works but I don't know how to total each fdd_name. For Example: ACME FD1 (38) ACME FD2 (30) ACME FD3 (20) <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"> GROUP BY f.fdd_name </cfquery> <cfoutput query="Zip"> #fdd_name# (Add Total Here)<br> </cfoutput> Thanks, Barry >You just need a JOIN and GROUP BY clause.  Check out the >subquery-to-join docs on MySQL's site. They have examples of how to do >exactly what you want. > >cheers, >barneyb

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

Hi Dave. Thanks for the code! Those two did not work. I got two queries to work perfect but I don't know how to display them. <!---Get all data---> <cfquery name="Story" datasource="#request.dsn#"> SELECT  F.fdd_id, F.fdd_name, F.fdd_zip, S.statename, S.abrev FROM  fddirectory F, states S WHERE  F.fdd_state = <cfqueryparam value="MD" cfsqltype="CF_SQL_VARCHAR">         AND F.fdd_state = S.abrev ORDER BY fdd_name </cfquery> <!---Count each fdd_name---> <cfquery name="Zip" datasource="#request.dsn#"> SELECT a.zipcode, f.fdd_zip, f.fdd_name, f.fdd_state, count(f.fdd_id) AS FDCount FROM articles a, fddirectory f WHERE a.zipcode = LEFT(f.fdd_zip, 5)         AND f.fdd_state = <cfqueryparam cfsqltype="cf_sql_varchar" value="MD"> GROUP BY f.fdd_name ORDER BY f.fdd_name ASC </cfquery> <!---Display---> <cfoutput query="zip"> #fdd_name# (#NumberFormat(zip.fdcount)# <br> </cfoutput> This works great but it only displays fdd_name when a.zipcode = LEFT(f.fdd_zip, 5). I would like to display something like this: <cfoutput> <!---Display all fdd_name---> #story.fdd_name# <!---Display this if GT '0'---> (#NumberFormat(zip.fdcount)# <br> </cfoutput> Thanks for your time. Barry

What didn't work with the second set of queries?  Did you get an error message?  That logic is sound.  There might be some syntax issues as I didn't test it... Dave Hi Dave. Thanks for the code! Those two did not work. I got two queries to work perfect but I don't know how to display them. <!---Get all data---> <cfquery name="Story" datasource="#request.dsn#"> SELECT  F.fdd_id, F.fdd_name, F.fdd_zip, S.statename, S.abrev FROM  fddirectory F, states S WHERE  F.fdd_state = <cfqueryparam value="MD" cfsqltype="CF_SQL_VARCHAR">         AND F.fdd_state = S.abrev ORDER BY fdd_name </cfquery> <!---Count each fdd_name---> <cfquery name="Zip" datasource="#request.dsn#"> SELECT a.zipcode, f.fdd_zip, f.fdd_name, f.fdd_state, count(f.fdd_id) AS FDCount FROM articles a, fddirectory f WHERE a.zipcode = LEFT(f.fdd_zip, 5)         AND f.fdd_state = <cfqueryparam cfsqltype="cf_sql_varchar" value="MD"> GROUP BY f.fdd_name ORDER BY f.fdd_name ASC </cfquery> <!---Display---> <cfoutput query="zip"> #fdd_name# (#NumberFormat(zip.fdcount)# <br> </cfoutput> This works great but it only displays fdd_name when a.zipcode = LEFT(f.fdd_zip, 5). I would like to display something like this: <cfoutput> <!---Display all fdd_name---> #story.fdd_name# <!---Display this if GT '0'---> (#NumberFormat(zip.fdcount)# <br> </cfoutput> Thanks for your time. Barry

Here is the error message: The string qZipTotals is not a valid ColdFusion variable name. Valid variable names must start with a letter and can only contain letter, numbers, and underscores. Barry

Hi Dave. I fixed the last error message. There was a space here: name=" qZipTotals " Now the data is displaying but it is not correct. This code below (ZIP)is correct: ----- Excess quoted text cut - see Original Post for more -----

Good Morning. This code works below but I'm not sure if there is a better way. Please let me know. <cfquery name="Zip" datasource="#request.dsn#"> SELECT a.zipcode, f.fdd_zip, f.fdd_name, f.fdd_state, f.fdd_id, count(f.fdd_id) AS FDCount FROM articles a, fddirectory f WHERE a.zipcode = LEFT(f.fdd_zip, 5)         AND f.fdd_state = <cfqueryparam value="#URL.statecodeID#" cfsqltype="CF_SQL_VARCHAR"> GROUP BY f.fdd_name ORDER BY f.fdd_name ASC </cfquery> <cfquery name="Story" datasource="#request.dsn#"> SELECT  F.fdd_id, F.fdd_name, F.fdd_zip, S.statename, S.abrev FROM  fddirectory F, states S WHERE  F.fdd_state = <cfqueryparam value="#statecodeID#" cfsqltype="CF_SQL_VARCHAR">     AND F.fdd_state = S.abrev ORDER BY fdd_name ASC </cfquery> <!---Display---> <cfoutput query="story"> #story.fdd_name# <cfloop query="zip"> <cfif story.fdd_id EQ zip.fdd_id> (#NumberFormat(zip.fdcount)#) </cfif> </cfloop> <br> </cfoutput> Thanks again for your time, Barry

Instead of this: <cfloop query="zip"> <cfif story.fdd_id EQ zip.fdd_id> (#NumberFormat(zip.fdcount)#) </cfif> </cfloop> Use a QofQ (Query of Queries) - it will be faster: <cfquery dbtype="query" name="qZipCheck">   SELECT fdcount FROM zip WHERE zip.fdd_id = <cfqueryparam value="#story.fdd#" cfsqltype="cf_sql_integer">   </cfquery> (#NumberFormat(qZipCheck.fdcount)#) This will avoid having to loop through your zip query for every time through your story loop.  Also, I assumed your fdd_id is an integer, if it's not, use cf_sql_varchar for the cfsqltype value instead. Dave Good Morning. This code works below but I'm not sure if there is a better way. Please let me know. <cfquery name="Zip" datasource="#request.dsn#"> SELECT a.zipcode, f.fdd_zip, f.fdd_name, f.fdd_state, f.fdd_id, count(f.fdd_id) AS FDCount FROM articles a, fddirectory f WHERE a.zipcode = LEFT(f.fdd_zip, 5)         AND f.fdd_state = <cfqueryparam value="#URL.statecodeID#" cfsqltype="CF_SQL_VARCHAR"> GROUP BY f.fdd_name ORDER BY f.fdd_name ASC </cfquery> <cfquery name="Story" datasource="#request.dsn#"> SELECT  F.fdd_id, F.fdd_name, F.fdd_zip, S.statename, S.abrev FROM  fddirectory F, states S WHERE  F.fdd_state = <cfqueryparam value="#statecodeID#" cfsqltype="CF_SQL_VARCHAR">     AND F.fdd_state = S.abrev ORDER BY fdd_name ASC </cfquery> <!---Display---> <cfoutput query="story"> #story.fdd_name# <cfloop query="zip"> <cfif story.fdd_id EQ zip.fdd_id> (#NumberFormat(zip.fdcount)#) </cfif> </cfloop> <br> </cfoutput> Thanks again for your time, Barry

Thanks Dave! Here is your final code: <cfoutput query="story"> #story.fdd_name# <cfquery dbtype="query" name="qZipCheck">        SELECT fdcount        FROM zip        WHERE zip.fdd_id = <cfqueryparam value="#story.fdd_id#" cfsqltype="cf_sql_integer"> </cfquery> <cfif #qZipCheck.fdcount# GT 0> (#NumberFormat(qZipCheck.fdcount)#) </cfif> <br> </cfoutput> Awesome. Barry