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

Open in new window



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.
Coco BeansDesignerAsked:
Who is Participating?
 
OMC2000Connect With a Mentor 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

Open in new window

0
 
Coco BeansDesignerAuthor 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.

All Courses

From novice to tech pro — start learning today.