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

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]

SELECT StudyCentre.studycentrecode, StudyCentre.studycentrename, count(teachercode) as TeachersFROM StudyCentre INNER JOIN Teacher ON StudyCentre.studycentrename = Teacher.studycentrenameGROUP BY StudyCentre.studycentrecode, StudyCentre.studycentrenameORDER 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/

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

Thanx you joelmartinez

it seems to work, i am a lot furhter anyway know, thanx