Two case statements with a group by function. Is there a way I can do the following: it's falling over because the outer case doesn't need a group by but the inner case does.
CASE WHEN COLLEGE.TYPE = 1 THEN 'Fee Paying'
WHEN COLLEGE.TYPE IN (2,3) THEN
CASE WHEN COUNT (DISTINCT LECTURE_TYPE) = 1 THEN 'FREE'
WHEN COUNT (DISTINCT LECTURE_TYPE) > 1 THEN 'FREE and FEE PAYING' END
FROM LECTURE, COLLEGE
WHERE LECTURE.COLLEGE_NO = COLLEGE.COLLEGE_NO
GROUP BY LECTURE.COLLEGE_NO
Basically is it's Fee paying use College Type but if it's free or free and fee paying use a count of lecture types to calculate the type of college
Any help would be greatly appeciated.