Mike, why do you would you need to know the exact years to use grouped output? This is not how it works. Grouped output simply groups the output by the defined column So if you group ny year, it will output all records with the same year together allowing you to then separate the output, add headings etc.
On Sun, Sep 26, 2010 at 4:34 PM, Mike Chabot <mcha...@gmail.com> wrote: > > 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 <r...@michaels.me.uk> > wrote: > > > > did the grouped cfoutpout not work for you then ? > > > > On Sun, Sep 26, 2010 at 4:55 AM, GLM <g...@glmdesigns.com> 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:mcha...@gmail.com] > >> 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 <g...@glmdesigns.com> 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:mcha...@gmail.com] > >> > 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 <g...@glmdesigns.com> 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:337565 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
