trying to group by but not working..

[Codeman0013]

help i'm trying to group by so i only get one doctor if he is in the same department 2 times but with different locations here is my sql:

SELECT DOC_FIRSTNAME, doctor_matrix.DOC_ID, DOC_PHOTO, DOC_LASTNAME, VISIBLE_ID, DOC_TITLE, doctor_matrix.DEPTCONTACT_ID
FROM doctors, doctor_matrix, departments_contact
WHERE ((departments_contact.DEPT_ID = MMColParam) AND (doctor_matrix.DEPTCONTACT_ID = departments_contact.DEPTCONTACT_ID) AND (doctor_matrix.DOC_ID = doctors.DOC_ID) AND (VISIBLE_ID = 1))
GROUP BY doctor_matrix.DOC_ID
ORDER BY DOC_LASTNAME ASC


but i get an error about DOC_LASTNAME not aggregate or something help me please...

 

[Melagan]

Codeman I'm trying to help you in another, almost identical thread in this forum. Please keep things in one thread so we can more efficiently help you.

~Melagan
______
"It's never too late to become what you might have been.

 

[lespaul]

GROUP BY is used when applying aggregate functions (SUM, COUNT, AVG) to a query. Say you want to know the number of doctors in a department:

SELECT DEPARTMENT, COUNT(*) FROM DOCTORS GROUP BY DEPARTMENT

This will return something like:

OB/GYN 20
Pediatrics 15
ER 10

So the query GROUPS each department. If there is a subdivision within the department and you want a count of Drs in the subdivision, you have to include that field in the GROUP BY as well:

SELECT DEPARTMENT, SUBDIVISION, COUNT(*) FROM DOCTORS GROUP BY DEPARTMENT, SUBDIVISION

(every field in the SELECT that is NOT an aggregate HAS to be included in the GROUP BY)

Why don't you post some sample data from your tables and your expected results so that we can "see" exactly what you are trying to accomplish.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lespaul]

and in both of these postings, someone has asked for sample data (from all the involved tables) and expected results.