Multiple counts in select

Transact-SQL
1

I have the following query that gets the count for students with codes 211. Now I want to add another count of those coded 230 in the same query. How do I do this?

SELECT 
                    count(*)
FROM STUDENTS stu
LEFT OUTER JOIN AB_STU sab ON sab.ID = stu.ID 
LEFT OUTER JOIN STUDENTS_EXT ust ON ust.ID = stu.ID
WHERE STU.ENROLL = 0 
AND stu.schoolid = 870
AND stu.grade = 10
AND (sab.GRANTSPROGRAM1 = 211 OR sab.GRANTSPROGRAM2 = 211 OR sab.GRANTSPROGRAM3 = 211)
2 
SELECT 
    SUM(CASE WHEN (sab.GRANTSPROGRAM1 = 211 OR sab.GRANTSPROGRAM2 = 211 OR sab.GRANTSPROGRAM3 = 211) 
        THEN 1 ELSE 0 END) AS Count_211,
    SUM(CASE WHEN (sab.GRANTSPROGRAM1 = 230 OR sab.GRANTSPROGRAM2 = 230 OR sab.GRANTSPROGRAM3 = 230) 
        THEN 1 ELSE 0 END) AS Count_230
FROM STUDENTS stu
LEFT OUTER JOIN AB_STU sab ON sab.ID = stu.ID 
LEFT OUTER JOIN STUDENTS_EXT ust ON ust.ID = stu.ID
WHERE STU.ENROLL = 0 
AND stu.schoolid = 870
AND stu.grade = 10
AND ((sab.GRANTSPROGRAM1 = 211 OR sab.GRANTSPROGRAM2 = 211 OR sab.GRANTSPROGRAM3 = 211)
  OR (sab.GRANTSPROGRAM1 = 230 OR sab.GRANTSPROGRAM2 = 230 OR sab.GRANTSPROGRAM3 = 230))
1 Like
3

Or

SELECT
   SUM(CASE WHEN 211 IN (sab.GRANPROGRAM1, sab.GRANPROGRAM2, sab.GRANPROGRAM3)
            THEN 1 
            ELSE 0 END
      ) Count211
 , SUM(CASE WHEN 230 IN (sab.GRANPROGRAM1, sab.GRANPROGRAM2, sab.GRANPROGRAM3) 
            THEN 1 
            ELSE 0 END
      ) Count230
FROM
   STUDENTS stu
LEFT OUTER JOIN 
   AB_STU sab 
   ON sab.ID = stu.ID 
LEFT OUTER JOIN
   STUDENTS_EXT ust
   ON ust.ID = stu.ID
WHERE STU.ENROLL = 0 
  AND stu.schoolid = 870
  AND stu.grade = 10
  AND (211 IN (sab.GRANPROGRAM1, sab.GRANPROGRAM2, sab.GRANPROGRAM3) 
         OR
       230 IN (sab.GRANPROGRAM1, sab.GRANPROGRAM2, sab.GRANPROGRAM3) 
      );
1 Like