Uh yes you can. Maureen's right.. add a select name, vote, count(vote) as tally from table group by name, vote
this will give you a count of votes by person by vote.. so if John voted 2 times for yes, 1 for no and 7 for abstain your output from the query will be John Yes 2 John No 1 John Abstain 7 On Mon, Mar 30, 2009 at 7:05 AM, Imperial, Robert <[email protected]> wrote: > > 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:4479 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
