I was about to recommend something similar to Chris here. Avoid using sub select statements in your where clause or select clause when you can. If the data is related, joining optimizes better.
Teddy On 1/19/07, Peterson, Chris <[EMAIL PROTECTED]> wrote: > > SELECT > A.Question, > A.Answer, > Count(*) as recordCount <--- Note! Don't use reserved words > like Count as a result > FROM > polls A left join pollAnswers B on a.pollID = B.pollID > GROUP BY > A.Question, > B.Answer > > That should work nice =) > > -----Original Message----- > From: Richard Cooper [mailto:[EMAIL PROTECTED] > Sent: Friday, January 19, 2007 8:31 AM > To: CF-Talk > Subject: Re: tallying SQL row entries > > Hi Doug & Greg. > > The query example didn't work for me. It always returned the count as 1 > :( > > I've made up some example data to show you want I'm trying to achieve: > > polls: > | pollID | Question | active | > ------------------------------ > | 1 | Red fav | 1 | > | 2 |like pets | 1 | > > pollAnswers: > | answerID | pollID | answer | > ------------------------------ > | 1 | 1 | Yes | > | 2 | 1 | Yes | > | 3 | 1 | No | > | 4 | 1 | Yes | > | 5 | 1 | Maybe | > | 6 | 1 | No | > | 7 | 2 | Dogs | > > OUTPUT > > | Question | Answer | Count | > ----------------------------- > | Red Fav | Yes | 3 | > | Red Fav | No | 2 | > | Red Fav | Maybe | 1 | > |like pets | Dogs | 1 | > > I tried grouping the data from the poll table and then used this in the > select: > COUNT(DISTINCT PR.questionAnswer) as answerCount > ,MIN(PR.questionAnswer) as questionAnswer > > But that hust returned the number of different answers. I'm looking into > some kind of sql loop but that's all new territory and I'm not sure if > that's the right direction > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266979 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
