Thanks I'll have a look at it though I don't think I am far off from what I need right now, just the one value that's not displaying for some odd reason. This one record I might add is the only one returned with a single "abstain" vote displayed though I've no clue why that would effect the other values in the display.
Bob -----Original Message----- From: Misha Mishyn [mailto:[email protected]] Sent: Monday, March 30, 2009 1:39 PM To: cf-newbie Subject: Re: query help Hi Bob, I do not have too much experience with MySQL, but I believe this article can help you define the way how you can get your result in single query: http://en.wikibooks.org/wiki/MySQL/Pivot_table Regards, Misha On Mon, Mar 30, 2009 at 2:06 PM, Bob Imperial <[email protected]> wrote: > > I got all that worked out and I've run into something that has me puzzled > with my output from this. I have 1 row that doesn't output anything in the > the yes vote column and I've checked using a single query to get just that > value, which shows the correct count. All others output correctly using > this: > > <cfquery name="getpackets" datasource="#dsdata#"> > select Id, name > from review_packets order by Id > </cfquery> > > <cfif getpackets.recordcount GT 0> > <table style="width:99%;" cellpadding="3" cellspacing="3"> > <tr class="hdr" style="font-size:13px; font-weight:bold;"> > <td style=" width:200px;">Name</td> > <td>yes</td> > <td>no</td> > <td>abstain</td> > <td>Total votes</td> > </tr> > <cfoutput query ="getpackets"> > <cfquery name="getvotes" datasource="#dsdata#"> > select vote, count(vote) as thisvote > from packet_votes where packet_id = #getpackets.id#<http://getpackets.id/#> > group by vote > </cfquery> > <tr> > <td>#getpackets.id# <http://getpackets.id/#> - # > getpackets.name# <http://getpackets.name/#></td> > <td><cfif getvotes.vote EQ 'yes'>#getvotes.thisvote#</cfif></td> > <td><cfif getvotes.vote EQ > 'no'>#getvotes.thisvote#</cfif></td> > <td><cfif getvotes.vote EQ > 'abstain'>#getvotes.thisvote#</cfif></td> > <td> > > </td> > </tr> > <br /> > </cfoutput> > </table> > </cfif> > > any ideas??? :( > > Thanks again - Bob > > >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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:4484 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
