Can't really do that since it's a single column with 3 possible values, guess I'll just handle it in the output via cfif.
Thanks for the help though, it knocked the cobwebs loose here :) -----Original Message----- From: Maureen [mailto:[email protected]] Sent: Friday, March 27, 2009 5:51 PM To: cf-newbie Subject: Re: query help Try adding a group-by vote clause to the query. On Fri, Mar 27, 2009 at 9:39 AM, Imperial, Robert <[email protected]> wrote: > > After checking what I had already this is similar to what I had worked up but > the totals I am trying to get are for 3 different values in the vote column > that can be stored; the 'vote' column is a varchar and can contain either > yes, no or abstain. So each packet_id will end up with multiple values each > of which I need to count. Does this make sense? I'm hoping to end up with > output that will roughly look something like this: > > Name yes no abstain > ---------------------------- > John Doe 4 1 3 > > Etc; > > Thanks! > > Bob > > > -----Original Message----- > From: Maureen [mailto:[email protected]] > Sent: Friday, March 27, 2009 2:10 AM > To: cf-newbie > Subject: Re: query help > > > Try this: > > <cfquery name="getpackets" datasource="datasourcename"> > select id, name, file_name from review_packets > order by id > </cfquery> > <cfoutput query ="getpackets"> > <cfquery name="getvotes" datasource="datasourcename"> > select count(vote) as thisvote from packet_votes > where packet_id = #getpackets.id# > </cfquery> > #getpacket.id# #getpacket.name# #getvotes.thisvote# > </cfoutput> > > > On Thu, Mar 26, 2009 at 9:19 AM, Bob Imperial <[email protected]> wrote: >> >> Hi folks, >> >> I have been asked to create a display page for tallied votes for some hr >> folks here. I know my approach with the queries is not at all productive but >> gives them something for the time being to look at :). I am trying to figure >> out how to make all of this a little more dynamic and hence more productive. >> I have 2 tables in a MySQL db and I basically wrote 3 different queries to >> get a recordcount of each vote for each packet_id, the long way around I >> know :( and I've been going in manually after voting to see what the >> packet_id is. It seems to me that I should be able to loop packet_votes >> somehow to get the packet_id and do some sort of aggregate function to count >> yes votes, no votes and abstain votes. Just cannot wrap my head around how >> to go about this. Any help, examples or insights into grasping a solution if >> there is one better would be appreciated. TIA >> >> packet_votes - >> id (PK) >> date_doted (varchar) >> name (varchar) >> packet_ID (varchar) >> vote (varchar) >> ----------------------------- >> review_packets - >> id (PK) >> name (varchar) >> file_name (varchar) >> >> Bob >> >> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4477 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
