kirchaj
Posts: 215
|
| Posted: 06/24/2008, 1:37 PM |
|
I have a table that links students to courses. I have be able to generate a report that gives me the count of students in each course. This is very helpful but I am needing more.
The report does not show classes that do not have any students in them. I can understand the report not being able to count something that is not there. But I really need a report that tells me what courses are empty. I would love for my existing report to display a 0 of the course is empty, but another way of doing this is acceptable.
Any ideas on how to get this to give me what I need?
Thanks.
TK
|
 |
 |
datadoit
|
| Posted: 06/24/2008, 2:25 PM |
|
Look at the SQL NOT EXISTS() statement.
|
|
|
|
maxhugen
Posts: 272
|
| Posted: 06/24/2008, 8:15 PM |
|
I think you need a LEFT JOIN from Course to Student in your query. That will include all Courses, whether or not any Students are in the Course.
_________________
Max
www.gardenloco.com | www.eipdna.com | www.chrisarminson.com |
|
|
|
kirchaj
Posts: 215
|
| Posted: 06/25/2008, 7:28 AM |
|
Maxhugen,
Thanks for the input. I have already tried that and what I have found is that the report is counting the NULLS and giving a total of 1. It is the strangest thing for me to get my head around. So I get a count of 1 if there is actually only one person in the class of if there are no people in the class.
Any ideas or explanations?
If not, I may have to contact support and get their input.
Thanks again.
TK
|
|
|
|
mentecky
Posts: 321
|
| Posted: 06/25/2008, 9:48 AM |
|
Quote kirchaj:
Maxhugen,
Thanks for the input. I have already tried that and what I have found is that the report is counting the NULLS and giving a total of 1. It is the strangest thing for me to get my head around. So I get a count of 1 if there is actually only one person in the class of if there are no people in the class.
Any ideas or explanations?
If not, I may have to contact support and get their input.
Thanks again.
TK
Try something like SUM(IF(column_name IS NULL, 0, 1)) AS student_count if the column is actually returning NULL. My guess is it may be returning 0 (zero) which COUNT will count as a value, in which case you could try SUM(IF(column_name = 0, 0, 1)) AS student_count
Rick
_________________
http://www.ccselite.com |
|
|
|
kirchaj
Posts: 215
|
| Posted: 06/26/2008, 6:53 AM |
|
Thanks Rick, I think you have hit the nail on the head. I will give this a try and see what happens.
TK
|
|
|
|
|
