SELECT 
AL2.ACADEMIC_YEAR, 
AL3.APPOINTMENT_TYPE, 
AL6.CIP_CATEGORY_CODE, 
AL6.CIP_CATEGORY_TITLE, 
AL6.CIP_FOUR_DIGIT_CODE, 
AL6.CIP_FOUR_DIGIT_TITLE, 
AL6.CIP_PROGRAM_CODE, 
AL6.CIP_PROGRAM_TITLE, 
AL5.DEPARTMENT_ABBREVIATION, 
AL4.INST_NAME, 
AL3.FACULTY_RANK, 
AVG ( AL1.AVERAGE_SALARY ), 
SUM ( AL1.FACULTY_FTE ) 
FROM WAREUSER.AAUDE_FACULTY_SALARY_DETAIL AL1, 
     WAREUSER.ACADEMIC_YEAR AL2, 
     WAREUSER.AAUDE_FACSAL_RANK AL3, 
     WAREUSER.INSTITUTION AL4, 
     WAREUSER.AAUDE_FACSAL_DEPARTMENT AL5,
     WAREUSER.CIP AL6 
WHERE 
(AL2.ACADEMIC_YEAR_KEY=AL1.ACADEMIC_YEAR_KEY  
 AND AL3.AAUDE_FACSAL_RANK_KEY=AL1.AAUDE_FACSAL_RANK_KEY 
 AND AL1.INSTITUTION_KEY=AL4.INSTITUTION_KEY 
 AND AL5.AAUDE_FACSAL_DEPARTMENT_KEY=AL1.AAUDE_FACSAL_DEPARTMENT_KEY 
 AND AL6.CIP_KEY=AL1.CIP_KEY)  
AND 
(AL2.ACADEMIC_YEAR='2000-2001' 
 AND AL3.FACULTY_RANK IN ('Assistant Professor', 'Associate Professor', 'Professor')
 AND AL4.INST_NAME IN ('Cal-Berkeley', 'Cal-Los Angeles', 'Illinois', 'Indiana', 
                       'Michigan', 'North Carolina', 'Texas', 'Washington', 'Wisconsin') 
 AND AL3.APPOINTMENT_TYPE='9-10 month' 
 AND AL6.CIP_CATEGORY_TITLE IN ('AGRICULTURAL BUSINESS AND PRODUCTION', 
                                'AGRICULTURAL SCIENCES')
) 
GROUP BY AL2.ACADEMIC_YEAR, 
         AL3.APPOINTMENT_TYPE, 
         AL6.CIP_CATEGORY_CODE, 
         AL6.CIP_CATEGORY_TITLE, 
         AL6.CIP_FOUR_DIGIT_CODE, 
         AL6.CIP_FOUR_DIGIT_TITLE, 
         AL6.CIP_PROGRAM_CODE, 
         AL6.CIP_PROGRAM_TITLE, 
         AL5.DEPARTMENT_ABBREVIATION, 
         AL4.INST_NAME, 
         AL3.FACULTY_RANK 
ORDER BY 7, 10