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:4472 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
