Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

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?
0
jeffmace
Asked:
jeffmace
  • 5
  • 3
  • 3
  • +4
1 Solution
 
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 3
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now