This topic is now archived and is closed to further replies.

a sql question

This topic is 5090 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I have these 2 tables STUDYCENTRE studycentrecode studycentrename TEACHER teachercode teachername studycentrename coursecode and i want for every studycentre the studycentrecode, the number of teachers and that number is descending order. Can you people help me with the sql statement? thanx in advance

Share this post

Link to post
Share on other sites
SELECT StudyCentre.studycentrecode, StudyCentre.studycentrename, count(teachercode) as Teachers
FROM StudyCentre
INNER JOIN Teacher ON StudyCentre.studycentrename = Teacher.studycentrename
GROUP BY StudyCentre.studycentrecode, StudyCentre.studycentrename
ORDER BY count(teachercode) desc
Disclaimer, I can''t remember off the top of my head of the order by comes before the group by or the other way around. Try both ways if it errors.

Also, just a note, I''d suggest using the studycentrecode as the foreign key in the Teacher table as opposed to the studycentrename (assuming the studycentrecode is the primary key).

Hope that helps,

Joel Martinez

Share this post

Link to post
Share on other sites
Which forum should i post it. I know here are a lot of clever guys.

I have
SELECT STUDYCENTRE.studycentrecode, COUNT (Teacher.Teachercode)

but i know this is the wrong way

Share this post

Link to post
Share on other sites