Archived

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

Pillejunior

a sql question

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
what have you got so far?

edit: And btw why did you post this in the nehe forum?

[edited by - Lantz on January 7, 2004 4:36:31 PM]

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
http://www.codecube.net/

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)
FROM STUDYCENTRE, TEACHER

but i know this is the wrong way

Share this post


Link to post
Share on other sites