Put every field from the select that is not an aggregate in your
group by and you get something understandable.
Jochem
Jochem,
I did this and it still gives me incorrect results. It gives me a count of 1
for each answer, no matter what the answer is. I'm just testing with one
evaluation
.
Adrian
-Original Message-
From: Will Tomlinson [mailto:[EMAIL PROTECTED]
Sent: 21 March 2007 10:45
To: CF-Talk
Subject: Re: Need More SQL help - DRIVING ME NUTS!
Put every field from the select that is not an aggregate in your
group by and you get something understandable.
Jochem
Jochem,
I
No been following this thread, but can you do the first two parts?
Yes, I've got this part working with Jochem's fine SQL.
What about the third part?
With each possible answer, show me a count of the ones that were selected
Nope
Do those then try to combine them.
Adrian
This is where the
Three things I noted...
1. There are a lot of duplicated columns in the result list...I removed
those to make it easier to read and debug
2. It looks to me like tblevalanswerresults needs to be joined to both
tblquestions and tblanswers...see below
3. When you join on tblEvaluations like you did
Thanks a ton Jim! I'll give it a shot later today and let you know the results.
Thanks!
Will
~|
Deploy Web Applications Quickly across the enterprise with ColdFusion MX7
Flex 2
Free Trial
Ok Jim, this is getting the results I'm after.
The counts are correct, everything looks right so far. I just needta test it
some more.
Would you please email me offlist?
WT at WTOMLINSON.com
Thanks a ton dude!
Will
~|
Will,
Which DB are you using and if it's not difficult can you also post db
scripts to create tables and populate some sample data.?
Thanks
Qasim
On 3/20/07, Will Tomlinson [EMAIL PROTECTED] wrote:
Ok, I'm really close on this. I've been workin on it for hours, changing
queries, not gettin
MySQL 5
Here's some table date as it looks in tblEvalAnswerResults. This is a many
table.
http://wtomlinson.com/evalQRYs/tableData.gif
Then here's what my query produces. I had to add E.evalID to get both
evaluations to show up in the results. I went ahead and did another evaluation,
so
And what I can't figure out is why this:
COUNT(EAR.answerID) AS numAnswers
would produce values of 1 for rows in which it should be 0.
Thanks,
Will
~|
Deploy Web Applications Quickly across the enterprise with ColdFusion
Will Tomlinson wrote:
And what I can't figure out is why this:
COUNT(EAR.answerID) AS numAnswers
would produce values of 1 for rows in which it should be 0.
Because you are using MySQL and MySQL has broken grouping. Your
statement is invalid SQL and the database should throw an error,
10 matches
Mail list logo
