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

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by