The grouped output doesn't work because the exact years are not known in the query output. Only the date range is known.
-Mike Chabot On Sun, Sep 26, 2010 at 5:20 AM, Russ Michaels <[email protected]> wrote: > > did the grouped cfoutpout not work for you then ? > > On Sun, Sep 26, 2010 at 4:55 AM, GLM <[email protected]> wrote: > >> >> Mike thanks for the lead. It had crossed my mind to build a numbers table >> but I didn't think it would work. (I've never used them myself.) >> >> I thought that it was somewhat excessive as there would have to be at least >> 365x220 rows (over 80,000). In the examples I gave I think one row per year >> (220) would be more than good enough but there are numerous exceptions >> (people dying in office, getting nominated to higher office, etc...) >> >> I'll give it a shot. >> >> Thx >> >> -----Original Message----- >> From: Mike Chabot [mailto:[email protected]] >> Sent: Saturday, September 25, 2010 10:50 PM >> To: cf-talk >> Subject: Re: cfoutput or cfloop? which is the more practical solution >> >> >> You can do this in SQL, although the methods I can think of involve >> advanced techniques so I hesitate to recommend them based on your >> stated SQL experience. One technique involves joining to a "numbers >> table" which will transform the start and end dates into one row per >> year. >> >> You can use the cfloop technique in your first post. It should get the >> job done. It isn't the fastest method, but it could be fast enough. >> One alternative to consider would be to have one big database query, >> then loop over the one query once and build up an array or a structure >> containing the data you want to output, then cfoutput the array or >> structure. >> >> -Mike Chabot >> >> On Fri, Sep 24, 2010 at 10:28 PM, GLM <[email protected]> wrote: >> > >> > Thanks Mike, >> > >> > I'm not a SQL expert but I don't see how. I can do something with >> particular >> > fields such as the year the person was elected (governorDateStart) or >> when >> > the person left office governorDateEnd >> > >> > >> > SELECT >> > governorParty, >> > COUNT(governorState), >> > governorDateStart >> > WHERE gender=female >> > >> > FROM >> > governors >> > >> > GROUP BY governorParty, governorDateStart >> > >> > And get something along the line of: >> > >> > 2001 - Republican - 2 >> > 2002 - Republican - 1 >> > 2003 - Democratic - 3 >> > 2003 - Republican - 1 >> > 2004 - Democratic - 1 >> > 2004 - Republican - 1 >> > 2005 - Democratic - 1 >> > 2006 - Republican - 1 >> > 2009 - Democratic - 1 >> > 2009 - Republican - 1 >> > >> > But what's desired is: how many are in office at a particular time. >> > For instance: >> > list the governors in 1889; >> > list the number of female governors in 2005 >> > >> > thx >> > >> > >> > >> > >> > -----Original Message----- >> > From: Mike Chabot [mailto:[email protected]] >> > Sent: Friday, September 24, 2010 7:35 PM >> > To: cf-talk >> > Subject: Re: cfoutput or cfloop? which is the more practical solution >> > >> > >> > Are you able to craft a query that will return the results you need >> > without ColdFusion having to do any extra parsing of it? That is the >> > first thing I would try. In your brief example it seems like that >> > would be solved using a GROUP BY statement in the query. >> > >> > -Mike Chabot >> > >> > On Fri, Sep 24, 2010 at 7:23 PM, GLM <[email protected]> wrote: >> >> >> >> I have a database with presidents, governors, etc. and need to be able >> to >> >> pull out information such as: >> >> >> >> >> >> >> >> Get the number of all female governors over the years and spit out >> > something >> >> on the order of: >> >> >> >> >> >> >> >> 1789 : 0 >> >> >> >> 1790 : 0 >> >> >> >> . >> >> >> >> 2005 : 10 >> >> >> >> . >> >> >> >> 2010 : 6 >> >> >> >> >> >> >> >> The database has dateStarted, dateEnded >> >> >> >> >> >> >> >> I can loop through this [SIMPLIFIED CODE] >> >> >> >> >> >> >> >> <cfloop> >> >> >> >> <cfquery> >> >> >> >> SELECT >> >> >> >> FROM >> >> >> >> WHERE year == #desiredYear# >> >> >> >> >> >> >> >> </cfquery> >> >> >> >> </cfloop> >> >> >> >> >> >> >> >> This seems like a foolish way to do this. Is there a better way to do >> it? >> > Is >> >> it better to make one query and then use CF to parse it >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > >> > >> > >> > >> >> >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337561 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
