Coco Beans
asked on
Two case statements with a group by function
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.
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.
SELECT LECTURE.COLLEGE_NO,
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
END ATT_SCHOOL_TYPE_CATEGORY
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER