Link to home
Start Free TrialLog in
Avatar of Coco Beans
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.

                 
   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.
ASKER CERTIFIED SOLUTION
Avatar of OMC2000
OMC2000
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Coco Beans
Coco Beans

ASKER

Perfect and so simple!