Help with multiple Counts in a SELECT Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with multiple Counts in a SELECT Query

Author

Topic

noam
Starting Member

2 Posts

Posted - 2005-11-29 : 03:51:14

hi
I want to make a query from 2 tables : ClassStudent , Class
In the Class table i have a record of a Teacher for each class
In the ClassStudet I have a record of a student <--> Class
now , i want to make a query that GROUP BY a Teacher and tells me how much Total Students for each Teacher , how much NEW students from each Teacher . i have a query that gives me only the Total part:

SELECT Class.Teacher, COUNT( tblStudentClass.IDNumber) AS Total
FROM Class INNER JOIN tblStudentClass ON Class.ClassNo = tblStudentClass.ClassNo
GROUP BY Group.Coach;

And it works Fine.
Now if i would add another column i would have to add WHERE to filter ther results as io want BUT HOW TO DO THAT I DONT KNOW !!
In tblStudentClass i have a field called dateOfEnter
so i want to add a new COUNT with filter Of NEW Students:
WHERE ( Month(tblStudentClass.dateOfEnter) = Month(DATE()))

I am doing that in MS Access 2003 do i have no COUNT(DISTINCT)...

thx 4 the help

noam
Starting Member

2 Posts

Posted - 2005-11-29 : 05:23:05

I have found a solution :
i have used SUM(IIf( .... ,1 ,0 )) because i didnt know that access does not support Count( Case ... )