Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Andrzejek on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MAX(... help)

Status
Not open for further replies.

lehmann

Technical User
Mar 24, 2003
1
CH
I cant get this function to perform how is it suppoed to be

SELECT MAX(SELECT Sum(Prescription.Quantity) gross
FROM Doctor, Prescription
WHERE Doctor.DoctorID = Prescription.PHN
GROUP BY Doctor.Name, Prescription.PCoName
HAVING (Prescription.PCoName="MedCorp"));
 
Hiya,

A having clause should only be used for aggregate functions, such as checking counts, mins, maxes etc.

Try :

SELECT MAX(SELECT Sum(Prescription.Quantity) gross
FROM Doctor, Prescription
WHERE Doctor.DoctorID = Prescription.PHN
AND (Prescription.PCoName="MedCorp")
GROUP BY Doctor.Name, Prescription.PCoName);

Hopefully that should give you what you are looking for.

HTH

Tim
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top