Nat, I figured it out, thanks for the help. I really appreciate it, this code rocks!
simon -----Original Message----- From: Plane, Nathaniel [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 2:18 PM To: CF-Talk Subject: RE: dynamic query try the following query, thats all you should need, that way you dont have to loop over a recordset. select count(*) as TheCount, b_country from connreporting group by b_country order by b_country Nat -----Original Message----- From: Attley, Simon (CMG-SF) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 5:12 PM To: CF-Talk Subject: RE: dynamic query Here is the query I'm using now: <cfquery name="qCountryTTL" datasource="connreports"> SELECT DISTINCT (b_country) AS CTTL FROM connreporting </cfquery> <cfloop query="qCountryTTL"> <cfquery name="qCCNT" datasource="connreports"> SELECT COUNT (b_country) AS #CTTL(countryname)# FROM connreporting </cfquery> </cfloop> I know the second part is all screwed up but I have tried everything and can't think of the solution. Thanks. -----Original Message----- From: Alex Ninan [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 1:22 PM To: CF-Talk Subject: RE: dynamic query Could you send the query you trying now? -----Original Message----- From: Attley, Simon (CMG-SF) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 12:22 PM To: CF-Talk Subject: dynamic query My boss told me this list is the heat! So here I am needing help. I am running a query to get distinct countries from a table. Nezt I would like to count how many entries in the table for each country and then display count like this: Brazil 5 USA 90 and so on. I have tried two different scenarios and keep getting stuck on the second count query. Any suggestion?? Oh great list members. Thanks, ______________________________________________________________________ Why Share? Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
