SQL advanced query - problem with single row aggregate sub query in select clause

Question

I'm trying to execute the following query but I receive a runtime error stating that:

"The column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

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?

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

[Edit]

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.

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

Answer 1

Your count(*) is an aggregate function and the expression for @lessonPlanResources is not (even though it's a query with COUNT(*)). Therefore, the expression must be included in the GROUP BY clause.

You can use common table expressions(CTE), which may help with readability too.

WITH LPR_CTE as
    (
    SELECT LessonPlanId, COUNT(*) as LessonPlanResourcesCount
    FROM dbo.LessonPlanResource
    GROUP BY LessonPlanId
    ),

    LP_CTE(
    SELECT lpt.LessonPlanId, COUNT(*) as LessonPlansCount
    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
    )

SELECT @lessonPlans = LP_CTE.LessonPlansCount
     , @lessonPlanResources = LPR_CTE.LessonPlanResourcesCount
FROM LP_CTE
JOIN LPR_CTE on LP_CTE.LessonPlanId = LPR_CTE.LessonPlanId

Answer 2

You would need to GROUP BY a.LessonPlanId and possibly change the first COUNT(*) to COUNT(*) OVER() depending on what exactly you are trying to do.

But that will presumably give multiple rows of results that you are then trying to assign to a single set of scalar variables. What are you trying to do exactly?

Answer 3

My guess would be that the @lessonPlanResources is linked to the LessonPlanId, which is not aggregate.

My solution would be to JOIN to that subtable, and have the returned column be a COUNT of it.

SELECT @lessonPlans = COUNT(*)
, @lessonPlanResources = SUM(zlpr.reses)
FROM
( 
    SELECT DISTINCT lpt.LessonPlanId, zlpr.reses
    FROM dbo.LearningTargetBreakout ltb
    JOIN dbo.LessonPlanLearningTarget lpt 
        on lpt.LearningTargetId = ltb.LearningTargetId
    JOIN (SELECT LessonPlanId, COUNT(*) reses FROM dbo.LessonPlanResource lpr) zlpr
        ON zlpr.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.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

Answer 4

The issue is that you are using a grouping function (count) with this subquery:SELECT COUNT(*) FROM dbo.LessonPlanResource lpr where lpr.LessonPlanId = a.LessonPlanId

However the reference to a.LessonPlanId is a non-grouped field. Group your query and you can reference that field.

Try this:

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
GROUP BY a.LessonPlanID

Answer 5

• Removing the COUNT from your outer select.
• Using COUNT DISTINCT in your inner select

---

SELECT 
   @lessonPlans = a.B,
   @lessonPlanResources = (SELECT COUNT(*) FROM dbo.LessonPlanResource lpr where lpr.LessonPlanId = a.LessonPlanId )
FROM
( 
    SELECT COUNT (DISTINCT, lpt.LessonPlanId) AS B
    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