Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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);
share|improve this answer
    
This query worked, but it was slow (about 17s). Somehow my original 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' is executing faster ~1.5s .. I thought it wasn't the best one. –  Stewie Mar 8 '12 at 17:59
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; 
share|improve this answer
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.

share|improve this answer
    
I didn't understand how this would work so I ran it.. it doesn't apply the group by and shows me millions of rows :( –  Stewie Mar 8 '12 at 18:09
    
This can't show millions of rows. Not if the queries in your question return only a few. –  ypercube Mar 8 '12 at 18:48
    
the figures in the question are fabricated. I have total 7.6million rows in student and 140K in classes and this one returns all rows. –  Stewie Mar 8 '12 at 19:31
    
You should end up with all classes in this –  HLGEM Mar 8 '12 at 19:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.