# 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.

``````   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.
###### Who is Participating?

Commented:
You could add aggregative functions (min,max) to outer case:
``````   SELECT LECTURE.COLLEGE_NO,
CASE WHEN max(COLLEGE.TYPE) = 1 THEN 'Fee Paying'
WHEN min(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
``````
0

DesignerAuthor Commented:
Perfect and so simple!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.