2 Replies - 14423 Views - Last Post: 12 October 2011 - 01:55 PM

#1 Duckington   User is offline

  • D.I.C Addict

Reputation: 172
  • View blog
  • Posts: 615
  • Joined: 12-October 09

Invalid in Select List [...] Not Contained In Agg Function of GROUP BY

Posted 12 October 2011 - 01:42 AM

Hi,

Was trying to run a query earlier, to select information about assessments a user has taken, as well as calculating the score of each assessment, which came out as this:

SELECT s.*, a.[assessment name], ((SUM(q.score) / SUM(q.out_of)) * 100) AS 'percentage_score'
FROM bksb_Sessions s
JOIN bksb_Assessments a ON a.ass_ref = s.assessment_id
JOIN bksb_QuestionResponses q ON q.session_id = s.session_id
WHERE s.userName = '88888888' 
AND s.status = 'Complete' 
AND a.[assessment group] = 1



Which obviously errored because it's doing SUM() but without a GROUP BY.

However, after adding a GROUP BY, for example:

GROUP BY s.session_id



I discovered that the new error was: Column 'bksb_Sessions.assessment_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

And when adding in that next one:

GROUP BY s.session_id, s.assessment_id



It just continued to tell me that everything I was selecting needed to be in the GROUP BY:

GROUP BY s.session_id, s.assessment_id, s.userName, s.status, s.dateCreated, a.[assessment name]



Is that really the case? If it's a large table you are selecting surely that would be a major hassle having to group by every single field? Is there no easier way to do that?

Cheers.

Is This A Good Question/Topic? 0
  • +

Replies To: Invalid in Select List [...] Not Contained In Agg Function of GROUP BY

#2 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7505
  • View blog
  • Posts: 15,553
  • Joined: 16-October 07

Re: Invalid in Select List [...] Not Contained In Agg Function of GROUP BY

Posted 12 October 2011 - 04:52 AM

Yep, it's a pain.

In a group by query, everything that's not an aggregate needs to be in the group by. The solution is the make a sub query of the group by.

e.g.
SELECT s.*, a.[assessment name], q.percentage_score
	FROM bksb_Sessions s
		JOIN bksb_Assessments a ON a.ass_ref = s.assessment_id
		JOIN (
			SELECT session_id, ((SUM(score) / SUM(out_of)) * 100) AS 'percentage_score'
			FROM bksb_QuestionResponses
			GROUP BY session_id
			) q ON q.session_id = s.session_id
	WHERE s.userName = '88888888' 
		AND s.status = 'Complete' 
		AND a.[assessment group] = 1


Was This Post Helpful? 0
  • +
  • -

#3 Duckington   User is offline

  • D.I.C Addict

Reputation: 172
  • View blog
  • Posts: 615
  • Joined: 12-October 09

Re: Invalid in Select List [...] Not Contained In Agg Function of GROUP BY

Posted 12 October 2011 - 01:55 PM

thanks
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1