TacoB0t TacoB0t - 2 months ago 8
SQL Question

SQL - Get average salary for each dpartment and number of courses taught in that department

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
DEPT, BUDGET

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;

Answer

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