Help with grouped query

I have a query that I need to add a column to, but when I do the error I receive asks me to include it in the grouped section which then messes up my output.   Here is my query.  It is outputing survey results.


<cfquery datasource="#DataTables#" name="getReplies">
SELECT     sum(poll_Replies.numReply) AS totalreplies, poll_Replies.answerID, poll_question.questionID, poll_question.question, poll_question.qtype, poll_choices.answerText, poll_Replies.textReply, poll_Replies.textReply_2
FROM         poll_Replies INNER JOIN
                      poll_Choices ON poll_Replies.answerID = poll_Choices.answerID INNER JOIN
                      poll_question ON poll_Choices.questionID = poll_question.questionID
WHERE     (poll_Replies.answerID IN
                          (SELECT     answerid
                            FROM          poll_choices
                            WHERE      questionid IN
                                                       (SELECT     questionid
                                                         FROM          poll_question
                                                         WHERE      pollID = #getPoll.pollID#)))
GROUP BY poll_Replies.answerID, poll_question.questionID, poll_question.question, poll_choices.answerText, poll_question.qtype, poll_Replies.textReply, poll_Replies.textReply_2
</cfquery>



The column I needed to add was (poll_Replies.textReply_2).  If I don't add it to the grouped line it gives me this error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Column &apos;poll_Replies.textReply_2&apos; is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But like i said, this messes up my output.   Is there a way to reformat this so that I do not have to include that in my grouped by line?
jeffmaceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrichmonCommented:
No.

You cannot have somehting in a select that has an aggregate function (such as COUNT, SUM, etc) and not include that in the group by.

One option is that you can run two queries and then use a query of queries to join the results afterwards on a comon ID such as maybe pollid or questionid in your case.
0
pinaldaveCommented:
Hi jeffmace,

well this is not possible but what you can do is ask this question in mssql may be they have some suggestion about splitting the query or something.

Regards,
---Pinal
0
Jerry_PangCommented:
>[Macromedia][SQLServer JDBC Driver][SQLServer]Column &apos;poll_Replies.textReply_2&apos; is invalid in the select list
>because it is not contained in either an aggregate function or the GROUP BY clause.

This is not SQL error not Coldfusion error. since you SUM()  poll_Replies.numReply, you have to include the other (all) collumns
in the group by clause. This is an SQL standard statement.

>But like i said, this messes up my output.   Is there a way to reformat this so that I do not have to include that in my grouped
>by line?

there is another way to format your sql statement and not to include them on group by clause.
you have to select (select statement) them(sorry i forgot the term.

so sumthing like

select
  (select sum(total) from employee b where b.employeeid=a.employeeid) as sumtotal
from
 employee a

maybe this one for you
<cfquery datasource="#DataTables#" name="getReplies">
SELECT    
(select sum(poll_Replies.numReply) FROM         poll_Replies INNER JOIN
                      poll_Choices ON poll_Replies.answerID = poll_Choices.answerID INNER JOIN
                      poll_question ON poll_Choices.questionID = poll_question.questionID
WHERE     (poll_Replies.answerID IN
                          (SELECT     answerid
                            FROM          poll_choices
                            WHERE      questionid IN
                                                       (SELECT     questionid
                                                         FROM          poll_question
                                                         WHERE      pollID = #getPoll.pollID#)))
)
AS totalreplies, poll_Replies.answerID, poll_question.questionID, poll_question.question, poll_question.qtype, poll_choices.answerText, poll_Replies.textReply, poll_Replies.textReply_2
FROM         poll_Replies INNER JOIN
                      poll_Choices ON poll_Replies.answerID = poll_Choices.answerID INNER JOIN
                      poll_question ON poll_Choices.questionID = poll_question.questionID
WHERE     (poll_Replies.answerID IN
                          (SELECT     answerid
                            FROM          poll_choices
                            WHERE      questionid IN
                                                       (SELECT     questionid
                                                         FROM          poll_question
                                                         WHERE      pollID = #getPoll.pollID#)))
</cfquery>

sumthing like that. The sql statement above is not the right sum though, i do not know what output you like. anyways, i leave this for you to figure out.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Jerry_PangCommented:
[quote]This is not SQL error not Coldfusion error.

i mean
This is an SQL error not Coldfusion error.
0
rajaloysiousCommented:
SELECT     sum(poll_Replies.numReply) AS totalreplies, poll_Replies.answerID, poll_question.questionID, poll_question.question, poll_question.qtype, poll_choices.answerText, poll_Replies.textReply, MAX(poll_Replies.textReply_2) /* MAXIMUM */
FROM         poll_Replies INNER JOIN
                      poll_Choices ON poll_Replies.answerID = poll_Choices.answerID INNER JOIN
                      poll_question ON poll_Choices.questionID = poll_question.questionID
WHERE     (poll_Replies.answerID IN
                          (SELECT     answerid
                            FROM          poll_choices
                            WHERE      questionid IN
                                                       (SELECT     questionid
                                                         FROM          poll_question
                                                         WHERE      pollID = #getPoll.pollID#)))
GROUP BY poll_Replies.answerID, poll_question.questionID, poll_question.question, poll_choices.answerText, poll_question.qtype, poll_Replies.textReply
0
pique_techCommented:
(came here from linked q:  http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21196000.html)

I'd suggest this as an approach to accomplish what I think you're trying for:

SELECT * FROM
(SELECT     sum(poll_Replies.numReply) AS totalreplies, poll_Replies.answerID, poll_question.questionID, poll_question.question, poll_question.qtype, poll_choices.answerText, poll_Replies.textReply
FROM         poll_Replies INNER JOIN
                      poll_Choices ON poll_Replies.answerID = poll_Choices.answerID INNER JOIN
                      poll_question ON poll_Choices.questionID = poll_question.questionID
WHERE     (poll_Replies.answerID IN
                          (SELECT     answerid
                            FROM          poll_choices
                            WHERE      questionid IN
                                                       (SELECT     questionid
                                                         FROM          poll_question
                                                         WHERE      pollID = #getPoll.pollID#)))
GROUP BY poll_Replies.answerID, poll_question.questionID, poll_question.question, poll_choices.answerText, poll_question.qtype, poll_Replies.textReply) summary
INNER JOIN
(SELECT poll_Replies.answerID, poll_Replies.textReply_2 FROM poll_Replies) text2
ON summary.answerID = text2.answerID
0
jeffmaceAuthor Commented:
Please refer to this link, I included images for you to refer to:
http://www.jhasim.com/ee/sql.html

The first image has the correct tally and output format.  What I want to do is add the textReply_2 to give the bulleted list output from the second image.  But as you can see the data gets a little strange probably becuase of the grouping I did in the query.  So what I want to do is have the formatting of the first image, with the bulleted list output from the second image.
0
pique_techCommented:
I assumed that you had already allowed for the possibility of one or many replies in text_Reply2.  If there is more than 1, then you will get multiple lines of output per entry, which may not be what you expected.  There's not really an elegant way to get what is basically a concatenated response (all the responses in one field) in SQL.  You'd need a bit more code (probably quite a bit) to get what you're wanting.
0
mrichmonCommented:
Or a query of queries may work....
0
Jerry_PangCommented:
I see. you need to add an aditional column.
i thought you wanted to remove the group by clause.

hmmm... try Distinct clause.

<cfquery datasource="#DataTables#" name="getReplies">
SELECT  Distinct poll_Replies.textReply_2,
sum(poll_Replies.numReply) AS totalreplies, poll_Replies.answerID, poll_question.questionID, poll_question.question,
poll_question.qtype, poll_choices.answerText, poll_Replies.textReply
FROM poll_Replies INNER JOIN
poll_Choices ON poll_Replies.answerID = poll_Choices.answerID INNER JOIN
poll_question ON poll_Choices.questionID = poll_question.questionID
WHERE (poll_Replies.answerID IN
(SELECT answerid
FROM poll_choices
WHERE questionid IN
(SELECT questionid
FROM poll_question
WHERE pollID = #getPoll.pollID#)))
GROUP BY poll_Replies.answerID, poll_question.questionID, poll_question.question, poll_choices.answerText, poll_question.qtype, poll_Replies.textReply, poll_Replies.textReply_2
</cfquery>
0
Jerry_PangCommented:
will analyze this thing.. can you try that one first?
0
Jerry_PangCommented:
hmm.. another question.

can you show us some code snipplet on how you output the
Other
Other
 Bullet1
 Bullet2
 Bullet3
 Bullet4
???

jeffmace, does the output on the Interactive SQL statement work? what database are you using?

i suspect there might be some problem with your cfoutput clause.
sumthing like
<cfoutput query="query1">
 <cfoutput>
 </cfoutput>
</cfoutput>

this will also give you the same output from your link.
0
jeffmaceAuthor Commented:
what i did was create a new table in my database to store the text replies and then loop through them when outputing the results.  Its probably a much better way of doing it anyhow.  I am not really sure about what to do with giving the answer to someone.  Does anyone have any suggestions.
0
pinaldaveCommented:
what kind of suggestion are you looking for Jeffmace?
Regards,
---Pinal
0
jeffmaceAuthor Commented:
Who the closest person was to giving me a correct answer.  I want to award points to someone, but I don't want people yelling at me that their answer was better.  So if you or anyone has some input that would be great.  Since I did not use any of the query suggestions and went a different and better route I would like to give the best solution the points.  Or give no one points.
0
pinaldaveCommented:
Two options:
1) Either go to community service and ask for refund.
2) Split points between expert according to who helped more to understand the problem or ignore the suggestion and go to other route.
As you have solved the problem yourself no one will mind anything (I GUESS).
Regards,
---Pinal
0
moduloCommented:
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.