avg query with a lookup 1

[evan1t]

I am trying to develop a query in WinSQL, and I am having a very hard time with it. I am hoping that someone here can help me.

I have two tables: 1) a doctors table, with a doc_id (numeric identifying code), and a first and last name for each doctor, and 2) a list of patients' stays in the hospital broken down by length of stay (stay_length) and doctor ID.

I can generate a report listing the average stay by doctor ID:
select s.doctor, avg(s.stay_length) "Average Stay"
from system.patient_stays s
group by s.doctor
order by avg(s.stay_length) desc;


What I want to do is annotate that listing with each doctor's name instead of just the ID number. Attempts have given me errors:

select s.doctor, d.firstname, d.lastname, avg(s.stay_length) "Average Stay"
from system.patient_stays s, doctors d
where doc_id=doctor
group by s.doctor
order by avg(s.stay_length) desc;

generates: Non-grouped column in SELECT or HAVING


If I leave out the group by clause, I get:
Some column are under set functions and some are not.



Help please...?

thank you!
Evan

 

[AlexCuse]

Hi Evan,

Try this out:

select s.doctor, d.firstname, d.lastname, avg(s.stay_length) "Average Stay"
from system.patient_stays s, doctors d
where doc_id=doctor
group by s.doctor, d.firstname, d.lastname
order by avg(s.stay_length) desc;

Everything in your select taht an aggregate is not applied to must be included in your group by.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[evan1t]

That did it!

Thank you very much, Alex!
Evan

 

[AlexCuse]

Glad it helped

Ignorance of certain subjects is a great part of wisdom