title: SQL advanced query - problem with single row aggregate sub query in select clause
body: <p>I'm trying to execute the following query but I receive a runtime error stating that:</p>
  
  <pre><code>"The column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
  </code></pre>
  
  <p>Line number two below is the one that fails with the error above. I don't understand why this query doesn't execute as no grouping should be required in line two as the output is just count(*), any clue as to what I need to change to get this working?</p>
  
  <pre><code>SELECT @lessonPlans = COUNT(*)
  , @lessonPlanResources = (SELECT COUNT(*) FROM dbo.LessonPlanResource lpr where lpr.LessonPlanId = a.LessonPlanId )
  FROM
  ( 
      SELECT DISTINCT lpt.LessonPlanId
      FROM dbo.LearningTargetBreakout ltb
      JOIN dbo.LessonPlanLearningTarget lpt 
          on lpt.LearningTargetId = ltb.LearningTargetId
      WHERE (CASE 
              WHEN ltb.LearningTargetId = @learningTargetId and @learningTargetId is not null THEN 1
              WHEN ltb.CategoryId = @categoryId and @categoryId is not null THEN 1
              WHEN ltb.Grade = @grade and @grade is not null THEN 1
              WHEN ltb.SubjectId = @subjectId and @subjectId is not null THEN 1
              ELSE 0 END) = 1
  ) a
  </code></pre>
  
  <p><strong>[Edit]</strong> </p>
  
  <p>Using a slight variation of Zeb's solution - here is the revised code that I ended up using, which yields one row with aggregates, which is what I was after.</p>
  
  <pre><code>SELECT @lessonPlans = ISNULL(COUNT(*), 0)
          , @lessonPlanResources = ISNULL(SUM(a.ResCount), 0)
  FROM
  ( 
      SELECT DISTINCT lpt.LessonPlanId, lpr.ResCount
      FROM dbo.LearningTargetBreakout ltb
      JOIN dbo.LessonPlanLearningTarget lpt 
          on lpt.LearningTargetId = ltb.LearningTargetId
      JOIN (SELECT LessonPlanId, COUNT(*) ResCount FROM dbo.LessonPlanResource lpr GROUP BY LessonPlanId) lpr
          ON lpr.LessonPlanId = lpt.LessonPlanId          
      WHERE (CASE 
              WHEN ltb.LearningTargetId = @learningTargetId and @learningTargetId is not null THEN 1
              WHEN ltb.CategoryId = @categoryId and @categoryId is not null THEN 1
              WHEN ltb.GradeId = @grade and @grade is not null THEN 1
              WHEN ltb.SubjectId = @subjectId and @subjectId is not null THEN 1
              ELSE 0 END) = 1
  ) a
  </code></pre>