A seemingly basic query that is giving me lots of problems. I had completed one similar but can't seem to get this right. I have three tables I'm looking up from:
CM - Describes departments in the company with fields
EM - describes employees with fields ID, NAME, DEPT, SALARY
CR - describes courses taught with fields Cnum, Department, ID
Fields in bold are primary keys.
I Thought this would work immediately, but it only returned one department with an average for the enitre salary column:
select CM.Dept, CM.Budget, avg(EM.Salary), count(distinct(CR.Cnum))
from CM join EM join CR
where CM.DEPT = EM.DEPT and CM.DEPT = CR.DEPT
group by CM.DEPT;
Just like @sgeddes hints your description implies a summary of your data by some fixed columns (CM.Dept, CM.Budget). This in SQL words implies to use group by. Group by requires to specify what columns you are grouping by and any others not in the grouping require an aggregation function, more info http://www.w3schools.com/sql/sql_groupby.asp. Perhaps something like shown below. That website also talks about the use of join.
select CM.Dept, CM.Budget, avg(EM.Salary), count(distinct(CR.Cnum)) from CM, EM, CR where CM.DEPT = EM.DEPT and CM.DEPT = CR.DEPT group by CM.Dept, CM.Budget