Each GROUP BY expression must contain at least one

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Each GROUP BY expression must contain at least one

Author

Topic

gvmk27
Starting Member

44 Posts

Posted - 2012-02-08 : 16:45:40

Hi I'm getting below message when trying to run the query

Each GROUP BY expression must contain at least one column that is not an outer reference.

Below is the SQL and it refers 2 external tables from a different database i.e k2.dbo.center & k2.dbo.Instructor.
Instead of referring external tables, if it refers the tables within the database it works fine....but when it refers external tables its giving the problem..

SELECT
k.CenterID,
os.OrderCenterType,
CASE k.ClassSubjectID
WHEN '10000137' THEN '#OFSUBJECT1STUDENTS'
WHEN '10000138' THEN '#OFSUBJECT2STUDENTS'
WHEN '10000139' THEN '#OFSUBJECT3STUDENTS'

END AS Subject,
(CASE os.OrderCenterType
WHEN 0 THEN (SELECT sum(StudentCount)
FROM ReportAStudentCount WHERE InstructorID = i.InstructorID AND ClassSubjectID = k.ClassSubjectID
Group By InstructorID )
WHEN 1 THEN (SELECT sum(StudentCount)
FROM ReportAStudentCount WHERE CenterID = k.CenterID AND ClassSubjectID = k.ClassSubjectID
Group By os.CenterID)
ELSE (SELECT sum(StudentCount)
FROM ReportAStudentCount WHERE CenterID = k.CenterID AND ClassSubjectID = k.ClassSubjectID
Group By k.CenterID)
END) as Count
FROM
ReportAStudentCount k
INNER JOIN InvOrderSummary os ON k.CenterID = os.CenterID
INNER JOIN K2.dbo.Center c ON c.CenterID = k.CenterID
INNER JOIN K2.dbo.Instructor i ON i.InstructorID = k.InstructorID
WHERE
BranchID = '10000023'
AND Year = 2011

Thanks in advance

Regs
MOhan

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-08 : 17:32:22

If I'm reading the query right, the GROUP BYs don't really make any sense (to me). The correlated sub-queries should only return 1 result, otherwise that'd be a different issue. So, I think you just need to remove the group bys.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-08 : 17:36:25

Do the group by on the column that is in the table you are selecting from. That should not make a difference in the grouping because you are doing inner joins to those columns. So


....
FROM ReportAStudentCount WHERE InstructorID = i.InstructorID AND ClassSubjectID = k.ClassSubjectID 
Group By InstructorID ) 
WHEN 1 THEN (SELECT sum(StudentCount) 
FROM ReportAStudentCount WHERE CenterID = k.CenterID AND ClassSubjectID = k.ClassSubjectID 
Group By CenterID) 
ELSE (SELECT sum(StudentCount) 
FROM ReportAStudentCount WHERE CenterID = k.CenterID AND ClassSubjectID = k.ClassSubjectID 
Group By CenterID) 
END) as Count 
....

Ideally, you should be qualifying the column names in those inner selects with the table names/aliases - for example:

....
WHEN 1 THEN (SELECT sum(ra.StudentCount) 
FROM ReportAStudentCount ra WHERE ra.CenterID = k.CenterID AND ra.ClassSubjectID = k.ClassSubjectID 
Group By ra.CenterID)
....

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-08 : 17:38:20

Go with what Lamprey said. If subqueries return more than one result, you are in trouble anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources