A join aggregate behaviour I can't understand

Question

Query 1:

 SELECT c.class_id,
       SUM
       (
        CASE
         WHEN s.student_id IS NULL THEN 0
         ELSE 1
        END
       ) as total_students 
  FROM classes c LEFT JOIN students s ON c.class_id = s.class_id
GROUP BY c.class_id;

result:

453 100
461 10
462 19
464 0
471 0
499 133

great !

Query 2: (added where condition to filter student types)

 SELECT c.class_id,
       SUM
       (
        CASE
         WHEN s.student_id IS NULL THEN 0
         ELSE 1
        END
       ) as total_students 
  FROM classes c LEFT JOIN students s ON c.class_id = s.class_id WHERE s.student_type='instate'
GROUP BY c.class_id;

result:

453 89
461 5
462 19
499 43

All the figures are right, except the ones with 0 counts vanish :!

why is that ?

Answer 1

The WHERE clause is checking s.student_type='instate'.

In a LEFT JOIN, everything on the right side is NULL, so the WHERE clause would not evaluate to anything.

If I may, I'd like to suggest a slight rewrite of your query:

Instead of this

SELECT c.class_id, 
       SUM 
       ( 
        CASE 
         WHEN s.student_id IS NULL THEN 0 
         ELSE 1 
        END 
       ) as total_students  
  FROM classes c LEFT JOIN students s ON c.class_id = s.class_id 
GROUP BY c.class_id; 

write it more compact like this

SELECT c.class_id, SUM(1-ISNULL(s.student_id)) as total_students
FROM classes c LEFT JOIN students s USING (class_id) 
GROUP BY c.class_id; 

Now, if you would like to filter on student_type and have the zeros to come back, you must rewrite the query like this:

SELECT
    A.class_id,
    IFNULL(B.total_students,0) total_students
FROM classes A LEFT JOIN
(
    SELECT c.class_id, COUNT(s.student_id) as total_students
    FROM classes c INNER JOIN students s USING (class_id)
    WHERE s.student_type='instate'
    GROUP BY c.class_id
) B USING (class_id);

UPDATE 2012-03-08 13:11 EST

Now I am curious

This is your query

SELECT class_id,
(
    SELECT count(*) FROM students a
    WHERE a.class_id=e.class_id
    AND a.student_type='instate'
) FROM classes e WHERE class_account='92';

Please Try This One

SELECT
    COUNT(DISTINCT e.class_id)
FROM
    (SELECT class_id FROM classes WHERE class_account='92') e
    INNER JOIN
    (SELECT class_id FROM students WHERE student_type='instate') a
    USING (class_id)
;

Make sure you have the following indexes before running this new query:

ALTER TABLE classes ADD INDEX (class_account,class_id);
ALTER TABLE students ADD INDEX (student_type,class_id);

Answer 2

SELECT c.class_id,       
       COUNT( s.student_id ) AS total_students    
FROM classes c 
  LEFT JOIN students s 
    ON c.class_id = s.class_id AND s.student_type='instate' 
GROUP BY c.class_id; 

Answer 3

SELECT c.class_id,       
 SUM(CASE          
    WHEN s.student_id IS NULL THEN 0         
    ELSE 1         
    END        
    ) as total_students    
FROM classes c 
LEFT JOIN students s ON c.class_id = s.class_id AND s.student_type='instate' 
GROUP BY c.class_id; 

You converted the join to an inner join by using the where clause.