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:266977
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
