help me with group by

  • Thread starter Thread starter Codeman0013
  • Start date Start date
C

Codeman0013

hey i'm trying to group by a doctor's last and first name is this possible
here is my sql i'm using...

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))
ORDER BY DOC_LASTNAME ASC

i had a GROUP BY doctors.DOC_LASTNAME, doctors.DOC_FIRSTNAME in there but
took it back out....

any help would be greatly appreciated...
 
When you are making a Group By, every fields (columns) in the Select
statement must be either part of the Group By or be the result of a group by
function such as Count(*), Max(), Min() or Sum(). So in your case you
should write something like:

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 DOC_LASTNAME, DOC_FIRSTNAME, doctor_matrix.DOC_ID, DOC_PHOTO,
VISIBLE_ID, DOC_TITLE, doctor_matrix.DEPTCONTACT_ID

ORDER BY DOC_LASTNAME ASC


However, some types of field such as Text, NText or Image cannot be part of
a Group By; in these cases, you must use a sub-query: you perform the group
by in the inner query and associate the remaining field in the outer query.
In your case, as you don't have to have a doctor id (something like
Doctor_ID), you could use the DOC_FIRSTNAME and DOC_LASTNAME to retrieve the
Doc_Photo in the outer query.

Finally, there is a flaw in your design: what's happens if you have two
doctors with the same name?
 
this is where my problem comes into play sylvain i i have one page where
right now i have 2 doctor's showing up but its just the same dr 2 times thats
y i was trying to do teh groupby any suggestions maybe to fix that??/

Sylvain said:
When you are making a Group By, every fields (columns) in the Select
statement must be either part of the Group By or be the result of a group by
function such as Count(*), Max(), Min() or Sum(). So in your case you
should write something like:

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 DOC_LASTNAME, DOC_FIRSTNAME, doctor_matrix.DOC_ID, DOC_PHOTO,
VISIBLE_ID, DOC_TITLE, doctor_matrix.DEPTCONTACT_ID

ORDER BY DOC_LASTNAME ASC

However, some types of field such as Text, NText or Image cannot be part of
a Group By; in these cases, you must use a sub-query: you perform the group
by in the inner query and associate the remaining field in the outer query.
In your case, as you don't have to have a doctor id (something like
Doctor_ID), you could use the DOC_FIRSTNAME and DOC_LASTNAME to retrieve the
Doc_Photo in the outer query.

Finally, there is a flaw in your design: what's happens if you have two
doctors with the same name?
hey i'm trying to group by a doctor's last and first name is this possible
here is my sql i'm using...
Click to expand...
[quoted text clipped - 11 lines]
any help would be greatly appreciated...
Click to expand...
Click to expand...
 
Every doctor should have a particular ID and you should make the Group By on
this ID.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Codeman0013 said:
this is where my problem comes into play sylvain i i have one page where
right now i have 2 doctor's showing up but its just the same dr 2 times
thats
y i was trying to do teh groupby any suggestions maybe to fix that??/

Sylvain said:
When you are making a Group By, every fields (columns) in the Select
statement must be either part of the Group By or be the result of a group
by
function such as Count(*), Max(), Min() or Sum(). So in your case you
should write something like:

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 DOC_LASTNAME, DOC_FIRSTNAME, doctor_matrix.DOC_ID, DOC_PHOTO,
VISIBLE_ID, DOC_TITLE, doctor_matrix.DEPTCONTACT_ID

ORDER BY DOC_LASTNAME ASC

However, some types of field such as Text, NText or Image cannot be part
of
a Group By; in these cases, you must use a sub-query: you perform the
group
by in the inner query and associate the remaining field in the outer
query.
In your case, as you don't have to have a doctor id (something like
Doctor_ID), you could use the DOC_FIRSTNAME and DOC_LASTNAME to retrieve
the
Doc_Photo in the outer query.

Finally, there is a flaw in your design: what's happens if you have two
doctors with the same name?
hey i'm trying to group by a doctor's last and first name is this
possible
here is my sql i'm using...
Click to expand...
[quoted text clipped - 11 lines]
any help would be greatly appreciated...
Click to expand...
Click to expand...
Click to expand...
 
well in the actual database each doctor has a unique id how would i make the
group by inside that sql to work?

Sylvain said:
Every doctor should have a particular ID and you should make the Group By on
this ID.
this is where my problem comes into play sylvain i i have one page where
right now i have 2 doctor's showing up but its just the same dr 2 times
Click to expand...
[quoted text clipped - 39 lines]
Click to expand...
 
If DOC_ID is the doc id:

SELECT DOC_ID, 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 DOC_ID, DOC_LASTNAME, DOC_FIRSTNAME, doctor_matrix.DOC_ID,
DOC_PHOTO,
VISIBLE_ID, DOC_TITLE, doctor_matrix.DEPTCONTACT_ID

ORDER BY DOC_LASTNAME ASC


--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Codeman0013 said:
well in the actual database each doctor has a unique id how would i make
the
group by inside that sql to work?

Sylvain said:
Every doctor should have a particular ID and you should make the Group By
on
this ID.
this is where my problem comes into play sylvain i i have one page where
right now i have 2 doctor's showing up but its just the same dr 2 times
Click to expand...
[quoted text clipped - 39 lines]
any help would be greatly appreciated...
Click to expand...
Click to expand...
Click to expand...
 
now this query gives me the following error:

You tried to execute a query that does not include the specified expression
'DOC_ID' as part of an aggregate function.

any suggestions??
 
You must use the real name for the field (or column) that you are using as
your doc id, not DOC_ID. I used "DOC_ID" only as an exemple.
 
i only have one problem though this is on a website that has several
different examples i cannot set it just to that one doctor becuase then all
of the others do not work is there any other way to do it?
 
From your previous post: « well in the actual database each doctor has a
unique id how would i make the group by inside that sql to work? »

All you have to do is to use this unique id.
 
yes they do its the doctors.DOC_ID field but can this be put into the sql so
its not the specific number and works on all the pages?
 
Back
Top