November 25, 2002 at 3:20 pm
Hello,
I have a query that uses grouping in which the results show a teacher's first name and last name and the school they are assigned to. With the help of joins, the records are filtered out based on a specific school number, and the fact that there is at least one matching test score for that teacher in a table called 'tblMMstudentTestScores'. The query looks like this:
***************************************
select
tt.schoolnum,
tt.lastname as teachLast,
tt.firstname as teachFirst
from student_data_main sd inner join tblMMstudentTestScores sc on sd.permnum=sc.permnum
inner join teacher_data_main tt on sd.teacherid=tt.teacherid
where sc.testscore is not null and tt.schoolnum = '366'
group by tt.schoolnum, tt.lastname,tt.firstname
order by tt.schoolnum
****************************************
What I want this query to do is to show me which teachers have put at least one score into table 'tblMMstudentTestScores'. The query does this as it should. The above code returns 27 records.
The next thing I need to do is to return the count of these records so that the result set might look something like this:
SchoolNum Number_of_Teachers
366 27
Can I incorporate COUNT in the query above to acheive this result, or is there some other way I need to approach this (use of a subquery)?
Thanks,
CSDunn
November 25, 2002 at 7:40 pm
First off in your current query I don't see the rela need to even have the GROUP BY since this is grouping all fiedls, dropping the group by and using SELECT DISTINCT should have the same result.
Now as for the new item. If this is in acddition to the current field you will only get a count of 1 per teacher unless you do a subquery which means you get more and more reads, or do a view on the current query and a view for the subquery and join them.
If this is all by itself the doing the same thing you have now do something like this maybe (sorry no server here to verify at the moment).
SELECT
tt.schoolnum,
COUNT(DISTINCT tt.lastname+tt.firstname) -- in case of same last or first name
from student_data_main sd inner join tblMMstudentTestScores sc on sd.permnum=sc.permnum
inner join teacher_data_main tt on sd.teacherid=tt.teacherid
where sc.testscore is not null and tt.schoolnum = '366'
group by tt.schoolnum, tt.lastname,tt.firstname
order by tt.schoolnum --Which you only have 1 schoole unless you multiple not needed.
Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply