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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With