On Mon, Mar 30, 2009 at 7:36 AM, Greg Morphis <[email protected]> wrote: > 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:4478 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
