Error: "Multiple columns are specified in an aggregated expression containing an outer reference."

Question

I am receiving this error when trying to execute the query below. Any ideas or suggestions?

Error:

Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

SELECT TestInstances.pkTestInstanceID AS 'pkTestInstanceID',
               bands.pkPerformanceLevelReportBandID AS 'BandID',
               bands.StackPosition AS 'StackPosition',
               (SELECT TOP 100 PERCENT SUM(CASE WHEN bands.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100/ CASE WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 THEN 1 ELSE COUNT(StudentScores_Subject.pkStudentScoreID) END
                FROM PerformanceLevelReportBands b 
                WHERE b.fkPerformanceLevelReportID = @intPerfLevelReportId
                ORDER BY SUM(CASE WHEN bands.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100/ CASE WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 THEN 1 ELSE COUNT(StudentScores_Subject.pkStudentScoreID) END) AS 'Percent',
         COUNT(StudentScores_Subject.pkStudentScoreID) AS 'Count'
         FROM StudentScores_Subject
                INNER JOIN StudentTests ON StudentScores_Subject.fkStudentTestID = StudentTests.pkStudentTestID
                INNER JOIN TestInstances ON  TestInstances.pkTestInstanceID = StudentTests.fkTestInstanceID
                INNER JOIN CAHSEE_TestPeriods ON CAHSEE_TestPeriods.pkTestPeriodID = TestInstances.fkTestPeriodID
                INNER JOIN PerformanceLevelReportBands bands ON bands.fkPerformanceLevelReportID = @intPerfLevelReportId
                LEFT JOIN MMARS_Web_TestInfo_California.dbo.PerfLevelReportBandCutScores cutScores ON cutScores.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID
                    AND cutScores.fkGradeID = @intGradeId
                    AND cutScores.fkTestSubjectID IN (SELECT id FROM @tempSubs)
                INNER JOIN PerfLevelReportBandComponents bandComponents ON bandComponents.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID 
                    AND((bandComponents.ScoreValue = StudentScores_Subject.ScoreValue) OR 
                        ((CAST(StudentScores_Subject.ScoreValue AS INT) BETWEEN bandComponents.minScore and bandComponents.maxScore)
                          OR 
                         (CAST(StudentScores_Subject.ScoreValue AS INT) BETWEEN cutScores.minScore and cutScores.maxScore)))
                RIGHT JOIN MM_SchoolYears ON MM_SchoolYears.pkSchoolYearID = TestInstances.fkSchoolYearID
        WHERE MM_SchoolYears.pkSchoolYearID IN (SELECT number FROM itot(@strYearIds, N','))
                AND StudentScores_Subject.fkStudentTestID IN (SELECT id FROM @tempTests)
                AND StudentScores_Subject.fkScoreTypeID = bandComponents.fkScoreTypeID
                AND StudentScores_Subject.fkTest_SubjectID IN (SELECT id FROM @tempSubs)
        GROUP BY TestInstances.pkTestInstanceID, bands.pkPerformanceLevelReportBandID, bands.StackPosition
        ORDER BY TestInstances.pkTestInstanceID, bands.pkPerformanceLevelReportBandID, bands.StackPosition

Answer 1

The problem is here you can't combine an outer and inner reference in an aggregate function

(SELECT TOP 100 PERCENT SUM(CASE WHEN bands.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100/ CASE
                                                                                                             
WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 THEN 1
                                                                                                            
ELSE COUNT(StudentScores_Subject.pkStudentScoreID)
                                                                                                        
 END
   FROM PerformanceLevelReportBands b
   WHERE b.fkPerformanceLevelReportID = @intPerfLevelReportId
   ORDER BY SUM(CASE WHEN bands.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100/ CASE
                                                                                              
WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 THEN 1
                                                                                              
ELSE COUNT(StudentScores_Subject.pkStudentScoreID)
                                                                                          
END) AS 'Percent'

So change it to

(SELECT TOP 100 PERCENT SUM(CASE WHEN bb.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100/ CASE
                                                                                                            
  WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 THEN 1
                                                                                                            
  ELSE COUNT(StudentScores_Subject.pkStudentScoreID)
                                                                                                        
 END
   FROM PerformanceLevelReportBands b JOIN PerformanceLevelReportBands bb
    ON bb.fkPerformanceLevelReportID =bands.fkPerformanceLevelReportID 
    AND b.fkPerformanceLevelReportID =bb.fkPerformanceLevelReportID
   WHERE b.fkPerformanceLevelReportID = @intPerfLevelReportId
   ORDER BY SUM(CASE WHEN bb.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100/ CASE
                                                                                              
 WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 THEN 1
                                                                                              
 ELSE COUNT(StudentScores_Subject.pkStudentScoreID)
                                                                                          
 END) AS 'Percent'

Here is a more thorough explanation.

Answer 2

I'd recommend commenting out bandComponents then cutScores, rerunning after removing each components and seeing where the query fails. Once you figure out where it's failing, then you can fix it.

Also, could be this line, the query in your Percent column.

ORDER BY SUM(CASE WHEN bands.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100 

I tried to organize your query a bit better to make it more legible.

SELECT 
    TestInstances.pkTestInstanceID                      AS 'pkTestInstanceID'
    , bands.pkPerformanceLevelReportBandID              AS 'BandID'
    , bands.StackPosition                               AS 'StackPosition'
    , (
        SELECT TOP 100 PERCENT 
            SUM( CASE 
                    WHEN bands.StackPosition = b.StackPosition 
                    THEN 1 
                    ELSE 0 
                    END) * 100 / 
                CASE 
                    WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 
                    THEN 1 
                    ELSE COUNT(StudentScores_Subject.pkStudentScoreID) 
                    END
            FROM PerformanceLevelReportBands b 
            WHERE b.fkPerformanceLevelReportID = @intPerfLevelReportId
            ORDER BY SUM(CASE WHEN bands.StackPosition = b.StackPosition THEN 1 ELSE 0 END) * 100 
            / 
            CASE 
                WHEN COUNT(StudentScores_Subject.pkStudentScoreID) = 0 
                THEN 1 
                ELSE COUNT(StudentScores_Subject.pkStudentScoreID) 
                END
            )                                           AS 'Percent'
    , COUNT(StudentScores_Subject.pkStudentScoreID)     AS 'Count'
 FROM 
    StudentScores_Subject
INNER JOIN 
    StudentTests ON 
    StudentScores_Subject.fkStudentTestID = StudentTests.pkStudentTestID
INNER JOIN 
    TestInstances ON 
    TestInstances.pkTestInstanceID = StudentTests.fkTestInstanceID
INNER JOIN 
    CAHSEE_TestPeriods ON 
    CAHSEE_TestPeriods.pkTestPeriodID = TestInstances.fkTestPeriodID
INNER JOIN 
    PerformanceLevelReportBands bands ON 
    bands.fkPerformanceLevelReportID = @intPerfLevelReportId
LEFT JOIN 
    MMARS_Web_TestInfo_California.dbo.PerfLevelReportBandCutScores cutScores ON 
        cutScores.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID
    AND cutScores.fkGradeID = @intGradeId
    AND cutScores.fkTestSubjectID IN (SELECT id FROM @tempSubs)
INNER JOIN 
    PerfLevelReportBandComponents bandComponents ON 
    bandComponents.fkPerformanceLevelReportBandID = bands.pkPerformanceLevelReportBandID 
    AND(
        (bandComponents.ScoreValue = StudentScores_Subject.ScoreValue) OR 
        (
            (CAST(StudentScores_Subject.ScoreValue AS INT) BETWEEN bandComponents.minScore and bandComponents.maxScore) OR 
            (CAST(StudentScores_Subject.ScoreValue AS INT) BETWEEN cutScores.minScore and cutScores.maxScore)
         )
        )
RIGHT JOIN 
    MM_SchoolYears ON 
    MM_SchoolYears.pkSchoolYearID = TestInstances.fkSchoolYearID
WHERE 
        MM_SchoolYears.pkSchoolYearID IN (SELECT number FROM itot(@strYearIds, N','))
    AND StudentScores_Subject.fkStudentTestID IN (SELECT id FROM @tempTests)
    AND StudentScores_Subject.fkScoreTypeID = bandComponents.fkScoreTypeID
    AND StudentScores_Subject.fkTest_SubjectID IN (SELECT id FROM @tempSubs)
GROUP BY TestInstances.pkTestInstanceID, bands.pkPerformanceLevelReportBandID, bands.StackPosition
ORDER BY TestInstances.pkTestInstanceID, bands.pkPerformanceLevelReportBandID, bands.StackPosition